Can SQL Server's MERGE statement update its merge_search_condition?

Viewed 27

I use MERGE for Upsert implementation. This code might be executed from two different application instances concurrently. The code:

MERGE foo as TARGET
USING (SELECT
    @Field1 as [field1],
    @Field2 as [field2],
    @SuperId as [super_id]) as SOURCE
ON TARGET.super_id = SOURCE.super_id
WHEN MATCHED THEN
UPDATE SET
    TARGET.[field1] = SOURCE.[field1],
    TARGET.[field2] = SOURCE.[field2]
WHEN NOT MATCHED BY TARGET THEN
INSERT (
    [field1],
    [field2],
    [super_id])
VALUES (
    SOURCE.[field1],
    SOURCE.[field2],
    SOURCE.[super_id])
OUTPUT
    inserted.common_id;

Can it somehow update the super_id field? Because that's exactly what I have. There was super_id 1 at the time of August, 23rd. And on September, 12th there was 2. I suspect that one machine was mergin on id = 1, and the second one on id = 2. Something went horribly wrong and id got replaced, because there's no rows with super_id = 1.

No other code accesses this table to somehow address the super_id field. One other way is for somebody to manually set the super_id field by their own hands. That is being investigated.

0 Answers
Related