On Dec 7, 2007, at 8:29 AM, 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.
>
> Thanks again.
The basic pseudo-code (not really SQL) is something like:
TRY:
run UPDATE
EXCEPT not found:
make INSERT
If you can give me some table layouts I can probably work out a
better example for you.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com