Tuesday, May 24, 2011

"UPSERT" (UPDATE where exists, INSERT otherwise)

The UPSERT command inserts rows that don't exist and updates the rows that do exist. The Word
UPSERT is a fusion of the words UPDATE and INSERT. UPSERT was officially introduced in the
SQL:2003 standard.

IF FOUND    THEN UPDATE ELSE    INSERT; 

In SQL Server 2008, Microsoft introduces the UPSERT functionality through the MERGE command.
Until now, this UPSERT functionality could only be made available in SQL Server through stored
procedures and cursors, etc.

This article illustrates how to take advantage of the UPSERT functionality via the MERGE command.

UPDATE
...

FROM ...
WHERE <condition>
-- race condition risk here
IF @@ROWCOUNT = 0
 
INSERT ...
or

IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0
 
-- race condition risk here
 
INSERT ...
ELSE
 
UPDATE ...

where < condition > will be an evaluation of natural keys. None of the above approaches seem to deal well
with concurrency. If I cannot have two rows with the same natural key, it seems like all of the above risk
inserting rows with the same natural keys in race condition scenarios.

Example:
SELECT * FROM Table_1, Table_2
--check first if the record already exists
IF EXISTS(SELECT * FROM Table_1 WHERE Table_1.ReqNr = Table_2.ReqNr)
--if it does, replace it
BEGIN
UPDATE Table_1 SET Table_1.Description = Table_2.Description
{WHERE Table_1.ReqNr = Table_2.ReqNr}
END
--if there is no such record yet, append new rows
ELSE
BEGIN
INSERT INTO Table_1(ReqNr, Description, FD)
SELECT ReqNr, Description, FD FROM Table_2
{WHERE ReqNr NOT IN(SELECT ReqNr FROM Table_2)}
END;

No comments :

Post a Comment