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.

Answer 1

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')
;