Problem running merge request not running

Question

I am trying to execute a merge request in sql server.

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

The request is the same:

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.*; 

Since my_table already has a row with field = "field_example" and date = '2017-01-04', I expect this query to update the 2 other columns of "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 

This way it looks good (to update it)

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

Any idea why?

Answer 1

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

Any idea why?

Perhaps you are querying another table / database or the transaction has been rolled back. The following is an example script assuming 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;