Ted Byers wrote:
--- Erik Jones <erik@xxxxxxxxxx> wrote:
On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
[snip]
What you want to do here for handling the update v.
insert is called
an "UPSERT". Basically, what you do is run the
update as if the row
exists and catch the exception that is thrown if it
doesn't at which
point you insert the record with the end date =
now(). After that
you can proceed normally with creating the new
record with start date
= now() and end date = NULL.
Thanks Eric. Do you know of an URL where this is
discussed or where I can find an example. None of my
books discuss this, and my search using google has so
far produced only noise.
You can do this with a conditional. Something like the following should
work.
IF
NOT (a query matching your data returns rows)
THEN
INSERT (your new data)
AFAIK, the developers are working on implementing the {MERGE, UPDATE OR
ON FAILURE INSERT, UPSERT} statement. Until then, you have to build
your upsert manually.
Colin
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
