Reputation: 617
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.
Upvotes: 0
Views: 47
Reputation: 1546
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.
Upvotes: 2