SQL Server: Insert row AND update previous duplicates

Question

I have a table that looks like:

Attributes
------------------------------------
Id | UserId | Key | Value | Active |

I would like to insert a new row into this table, and for every row that has the same UserId and Key, I'd like to set Active to 0.

The merge statement seems like a convenient choice here, but I'm not sure how to perform two actions at once, ie:

MERGE INTO Attributes AS Target
USIN (<values>) AS Source (<columns>)
    ON Target.Key = Source.Key AND Target.UserId = Source.Uid
WHEN MATCHED <update, then insert>
WHEN NOT MATCHED <insert>

I could definitely do this in two queries, but I was wondering if there's a way to do what I'm proposing in one.

It's still two separate actions, so what is the gain in using one statement? Also please read this in full. MERGE does not buy you anything here. — Aaron Bertrand
– Aaron Bertrand, Commented Apr 21, 2015 at 19:05

Ben Jaspers · Accepted Answer · 2015-04-21 18:52:46Z

2

You could possibly do it in a single MERGE statement, but it won't be pretty. MERGE will perform only one action for every row in Source, so it won't do both MATCHED and NOT MATCHED and you can't both update and insert for MATCHED; you would need to select one row for every existing row, plus one row for the row you actually want to insert in order to do what you want using just a MERGE.

You are better off just using 2 queries.

answered Apr 21, 2015 at 18:52

Ben Jaspers

1,5461 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TheMonarch Over a year ago

Good call. I'll use two queries.

mstbaum Over a year ago

I'm pretty sure you can do MATCHED and NOT MATCHED in the same MERGE statement. WHEN MATCHED THEN <do something> WHEN NOT MATCHED THEN <do something else>

Ben Jaspers Over a year ago

Yes, I meant that for each row, MERGE only does one or the other.

mstbaum Over a year ago

Yeah sorry I just reread your answer and realized that's what you meant.

TheMonarch Over a year ago

Either way I think Ben Jaspers is right; I should be doing this in two queries for readability if nothing else. This won't be a common enough action for extra overhead to matter (and a merge that complex might be inefficient on its own anyway).

|

Collectives™ on Stack Overflow

SQL Server: Insert row AND update previous duplicates

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related