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.