If Insert Fails, then Update

[Pat Backowski]

Hi Everyone,

I have a table that I want to INSERT the entries into another table, easy
enough with an Append Query, but if the insert fails, due to duplicate keys,
I just want to update a few fields.

Any ideas?

Thanks in Advance for your kind consideration
Pat.

 

[Marshall Barton]

I have a table that I want to INSERT the entries into another table, easy
enough with an Append Query, but if the insert fails, due to duplicate keys,
I just want to update a few fields.

Sounds like a two step process.

First, update the existing records:

UPDATE tbl2
SET tbl2.fa = tbl1.fa
tbl2.fb = tbl1.fb
. . .
FROM tbl1 INNER JOIN tbl2
ON tbl1.key = rbl2.key

Then append the new records:

INSERT INTO tbl2
SELECT tbl1.*
FROM tbl1 LEFT JOIN tbl2
ON tbl1.key = tbl2.key
WHERE tbl2.key Is Null

 

[John W. Vinson]

Hi Everyone,

I have a table that I want to INSERT the entries into another table, easy
enough with an Append Query, but if the insert fails, due to duplicate keys,
I just want to update a few fields.

Any ideas?

Thanks in Advance for your kind consideration
Pat.

There's actually a very sneaky way to do a combined update-append query: use a
Left Join. Let's say you want to update table DEST with new or modified
records from table SOURCE:

UPDATE SOURCE
LEFT JOIN DEST
ON DEST.keyfield = SOURCE.keyfield
SET
DEST.keyfield = SOURCE.keyfield,
DEST.thisfield = SOURCE.thisfield,
DEST.thatfield = SOURCE.thatfield;

Be sure to set the joining field (which I've called keyfield) - it does no
harm if the record already exists, it'll just set the field to itself, but it
adds the primary key value if it doesn't.

Note that this will NOT work if the keyfield is an autonumber.

John W. Vinson [MVP]

 

[Pat Backowski]

John, That's sheer wizardry! How the heck an Update of SOURCE can end up
doing Inserts into Dest is beyond my understanding (I know - its all in the
join!) - Best of all, It WORKS!!!
Kudos, Cheers, and Bravissimo
Pat.

 

[John W. Vinson]

John, That's sheer wizardry! How the heck an Update of SOURCE can end up
doing Inserts into Dest is beyond my understanding (I know - its all in the
join!) - Best of all, It WORKS!!!

UPDATE SOURCE LEFT JOIN DEST
ON DEST.keyfield = SOURCE.keyfield
SET
DEST.keyfield = SOURCE.keyfield,
DEST.thisfield = SOURCE.thisfield,
DEST.thatfield = SOURCE.thatfield;

Well, you're actually updating DEST; the SET clause is what does the updating.
What the JOIN does is returns all records in SOURCE (where you're updating
from); if there is a matching record in DEST it joins it (so you update the
existing record); if there isn't a matching record in DEST it joins the SOURCE
record to a new, NULL, empty record in DEST and updates *that*.

I learned this trick years ago from some forgotten wizard, probably on this
very newsgroup, and had exactly the same reaction.

John W. Vinson [MVP]