Ted Byers wrote:
> --- Erik Jones <erik@myemma.com> 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