Equivalent of ON DUPLICATE KEY UPDATE / UPSERT

Question

I'm a PostgreSQL user that's new to SQL Server. I need to replicate ON DUPLICATE KEY UPDATE functionality (sometimes called UPSERT). I've got a table of users (simplified here) with age and sex.

Using this sample query, but changing the id as needed, the UPDATE functionality works, but the INSERT doesn't. There's no error, it just says 0 rows affected.

MERGE
    users AS target 
USING
    (SELECT id FROM users WHERE id=222) AS source
ON
    target.id = source.id

WHEN MATCHED THEN 
    UPDATE SET 
        target.id  = source.id,
        target.age = 33,
        target.sex = 'M'

WHEN NOT MATCHED THEN 
    INSERT (id, age, sex) VALUES (222, 33, 'M')
;

If it matters (maybe there's some easier way), I'm using Python3 in linux.

P.S. I looked at the other UPSERT in SQL Server questions here in StackOverflow. That's how I got this syntax. I couldn't understand the problem here through them, though.

if the id matches only `update` would be executed and not `insert`. — Vamsi Prabhala, Jan 26 '17 at 20:37

score 3 · Accepted Answer · edited Jul 16 '23 at 22:29

I ended up using some info from this question to solve it. It doesn't address it exactly, but it helped me see an issue with my subselect (...) AS source. Basically, (apparently) the USING implicitly assumes the source table and by explicitly specifying it with FROM users WHERE ... I was blocking SQL Server from examining it. That's my best understanding anyway.

The point is, this query works: run it once and it inserts a new user with (555, 55, 'M'). Run it again and the same record is updated to (555, 22, 'F').

Also, apparently MERGE can have issues with concurrency at high rates, so the linked question suggested using HOLDLOCK, which I have here.

MERGE INTO users WITH (HOLDLOCK) AS target
    USING 
        (SELECT 555 AS id) AS source 
ON 
    (target.id = source.id)

WHEN MATCHED THEN 
        UPDATE SET 
            target.id  = source.id,
            target.age = 22,
            target.sex = 'F'
WHEN NOT MATCHED THEN 
        INSERT (id, age, sex) VALUES (555, 55, 'M')
;

Fuzzy · Answer 2 · 2017-01-26T21:05:46.213

2

You will not be able to insert a row that does not exist in a table when merging a table onto itself:

I suggest doit the following:

DECLARE @id INT = 222, @age int = 33, @sex VARCHAR(10) = 'M'


IF EXISTS (SELECT id FROM users WHERE id=@id)
BEGIN 
    UPDATE users SET age = @age, sex = @sex
END
ELSE 
BEGIN
    INSERT INTO users (id, age, sex) VALUES (@id, @age, @sex)
END

and if you are calling it from another application you may want a stored procedure so you may want to create a stored procedure to do this like so:

CREATE PROCEDURE sp_UpdateInsertUsers @id  INT 
                             , @age INT 
                             , @sex VARCHAR(10) 
AS
    BEGIN
        SET NOCOUNT ON;
        IF EXISTS
               (SELECT id
                FROM   users
                WHERE  id = @id)
           BEGIN
              UPDATE users
                SET  age = @age, sex = @sex;
           END;
        ELSE
           BEGIN
              INSERT INTO      users(id
                               , age
                               , sex)
              VALUES
                    (@id
                   , @age
                   , @sex);
           END;
    END;
GO

Calling the procedure like so:

EXECUTE sp_UpdateInsertUsers @id  = 222
                             , @age = 33
                             , @sex = 'M'

edited Jan 26 '17 at 21:05

answered Jan 26 '17 at 20:45

Fuzzy

3,810
2
15
33

Unfortunately, no change. Still (0 row(s) affected) when not matched. – Sir Robert Jan 26 '17 at 20:52
so you are merging a table onto itself? and if you have a key existing then you update but if no such record exist then you want to insert? If the latter is correct then I don't think a merge is what you need. – Fuzzy Jan 26 '17 at 20:55
I'm open to another solution if you have one :) I just need to insert a record, or update if that record's id already exists. – Sir Robert Jan 26 '17 at 20:57
Thanks for your help. I found a solution that's a bit shorter. I'm not entirely sure of the underlying reason it works, but it's intelligible to me in its form. – Sir Robert Jan 26 '17 at 21:12
Can you please share that solution. I'll be interested in knowing what it is – Fuzzy Jan 26 '17 at 21:12

score 1 · Answer 3 · edited Jul 16 '23 at 22:29

An example with bound parameters in ASP.NET for updating products in a cart:

MERGE 
INTO [shoppingcart] WITH (holdlock) AS target 
using        ( 
                    SELECT @ProductID AS ProductID) AS source 
ON ( 
                          target.productid = source.productid) 
WHEN matched THEN 
UPDATE 
SET              target.productid = source.productid, 
                 target.quantity = target.quantity + 1, 
                 target.date = @Date, 
                 target.clientid = @ClientID 
WHEN NOT matched THEN 
INSERT 
       ( 
              quantity, 
              date, 
              clientid, 
              productid 
       ) 
       VALUES 
       ( 
              @Quantity, 
              @Date, 
              @ClientID, 
              @ProductID 
       );

Equivalent of ON DUPLICATE KEY UPDATE / UPSERT

3 Answers3