SQL to Insert or update if exist

[MPEcho]

Greetings all,

I have (with villeroy's generous help) created tables, query's etc to add due dates to a project. The duedates are stored in table DUEDATES Values are inserted from a view "zDateTemplate1Filter" using the following:

Code: Select all

INSERT INTO DUEDATES (SELECT ""zDateTemplate1Filter"".*, NULL AS ID, NULL AS DONE FROM ""zDateTemplate1Filter"")

I can also, update using this:

Code: Select all

UPDATE "DUEDATES" 
SET "DUE" =  Select "DUE"  FROM "zDateTemplate1Filter" WHERE "DUEDATES"."REMARK" =  "zDateTemplate1Filter"."DESCR" 

(zDateTemplate1Filter has fields PID | DUE | DESCR )
what I am trying to do is one sql that will update if existing on a per record basis, otherwise insert the record. In other words, for each record in zDateTemplate1Filter, if there is record in DUEDATES that matches both PID and DESCR, then update, otherwise insert.

Any thoughts on the best way to construct?

[UnklDonald418]

There are examples on page 162 of the guide_HSQLDB_2.40 that might help you.
http://hsqldb.org/web/hsqlDocsFrame.html

[chrisb]

MPEcho, assuming that the field "PID" is unique then the code below may solve the issue.

Code: Select all

update DUEDATES set DUE =
(select DUE from "zDateTemplate1Filter" where PID = DUEDATES.PID and DESCR = DUEDATES.REMARK)
where DUEDATES.PID in
(select PID from "zDateTemplate1Filter" where PID = DUEDATES.PID and DESCR = DUEDATES.REMARK)
;
insert into DUEDATES
(
select Z.*, null ID, null DONE from "zDateTemplate1Filter" Z
where Z.PID not in
(select PID from DUEDATES)
);

[MPEcho]

There are examples on page 162 of the guide_HSQLDB_2.40 that might help you.
http://hsqldb.org/web/hsqlDocsFrame.html

Thanks for the pointer. I'll do some work with it.

[MPEcho]

MPEcho, assuming that the field "PID" is unique then the code below may solve the issue.

Thanks chrisb, yes PID is unique to each project. DUEDATES has a collection of dates for all projects. Different approach from the HSQLDB manual linked by unkdonald. I'll take a look and see if that works. Thanks.