On Dec 7, 2007, at 8:29 AM, 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.
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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
