issue with merge update query not performing as it should

Question

I am trying to perform a merge update query in sql server.

The table "my_table" has 4 columns "field" (character),"date"(date),"val"(numeric),"revision"(datetime)

The query is as such:

MERGE "my_table" AS Target USING ( VALUES ('field_example','2017-01-04','0','2017-01-03 12:02:02')) AS Source ("field","date","val","revision")
                   ON (Target."field" = Source."field" AND Target."date" = Source."date")
                   WHEN MATCHED 
                   THEN UPDATE SET Target."val" = Source."val",Target."revision" = Source."revision"
                   WHEN NOT MATCHED BY TARGET 
                   THEN INSERT ("field","date","val","revision")
                   VALUES (Source."field", Source."date", Source."val", Source."revision")
                   OUTPUT $action, Inserted.*, Deleted.*; 

As there is already a row in "my_table" with field="field_example" and date='2017-01-04', I am expecting this query to update the 2 other columns "val","revision".

I get the following query output:

$action            field       date            revision val          field.1     date.1          revision.1  val.1
1  UPDATE field_example 2017-01-04 2017-01-03 12:02:02   0 field_example 2017-01-04 2017-01-03 10:09:25 161250

So it looks good (to have updated as it should)

However when I look in the database, the row has not been updated (= val is still 161250 instead of 0, and revision is still 2017-01-03 10:09:25)

Any idea why?

Answer 1

However when I look in the database, the row has not been updated (= val is still 161250 instead of 0, and revision is still 2017-01-03 10:09:25)

Any idea why?

Perhaps you are querying a different table/database or the transaction was rolled back. The script below works as expected, guessing at actual data types.

CREATE TABLE dbo.my_table(
     "field" varchar(100)
     ,"date" date
     ,"val" int
     ,"revision" datetime
     );

INSERT INTO my_table ("field","date","val","revision")
    VALUES ('field_example','2017-01-04','161250','2017-01-03 10:09:25');

MERGE "my_table" AS Target USING ( VALUES ('field_example','2017-01-04','0','2017-01-03 12:02:02')) AS Source ("field","date","val","revision")
    ON (Target."field" = Source."field" AND Target."date" = Source."date")
    WHEN MATCHED THEN
        UPDATE SET Target."val" = Source."val",Target."revision" = Source."revision"
    WHEN NOT MATCHED BY TARGET THEN
        INSERT ("field","date","val","revision")
            VALUES (Source."field", Source."date", Source."val", Source."revision")
    OUTPUT $action, Inserted.*, Deleted.*; 

SELECT "field","date","val","revision" 
FROM my_table;