CODEWITHSUNDEEP
sqlsql-servert-sqlmerge
Brainlock
Brainlock

Reputation: 243

MERGE statement is not updating

When executing the following on SQL Server, I would expect the NULL be updated with value 88888888 but it inserts a new row instead. Can someone please tell me what I'm doing wrong?

USE tempdb;

CREATE TABLE TableA (ColA CHAR(8), ColB CHAR(8))
CREATE TABLE TableB (ColA CHAR(8), ColB CHAR(8))

INSERT INTO TableA VALUES  ( 'XXXXXXXX', NULL )
INSERT INTO TableA VALUES  ( 'XXXXXXXX', '88888888')

MERGE INTO TableB AS T
USING TableA AS S
ON T.ColA = S.ColA and T.ColB = S.ColB
WHEN MATCHED 
    THEN
      UPDATE
         SET T.ColA = S.ColA, T.ColB = S.ColB
WHEN NOT MATCHED BY TARGET 
    THEN
      INSERT (ColA, ColB) VALUES (S.ColA, S.ColB);

SELECT  * FROM    TableA
SELECT  * FROM    TableB

DROP TABLE TableA
DROP TABLE TableB

Many Thanks! Best Regards, Steve

Upvotes: 1

Views: 3432

Answers (3)

Eric
Eric

Reputation: 81

The target table, TableB, is empty. Change this line: Old: INSERT INTO TableA VALUES ( 'XXXXXXXX', NULL ) New: INSERT INTO TableB VALUES ( 'XXXXXXXX', NULL )

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

a) Tables are inherently unordered. Even if (as you seem to expect) the merge proceeded one row at a time (see c below), there's no reason it wouldn't see the row with both columns non-null before the row with a null ColB

b) Your ON clause specifies a match on both ColA and ColB. Both rows are different in at least one of these, so I'd expect neither row to match a row in TableB, and

c) SQL statements apply as if all rows are affected in parallel, at the same time. It doesn't process one row, and then move onto the next. So there's never an occasion where there's exactly one row in TableB.

Unfortunately, what you've posted is obviously a "toy" example, so I can't offer any recommendations for how to fix it - you've not told us what the real situation is, what the real data looks like, why you're using MERGE, etc.

Upvotes: 5

Sonam
Sonam

Reputation: 3466

MERGE INTO TableB AS T
USING TableA AS S
ON T.ColA = S.ColA 
WHEN MATCHED 
THEN
  UPDATE
     SET  T.ColB = S.ColB

Please remove this from join: and T.ColB = S.ColB.

And also you can remove T.ColA = S.ColA from update as these columns are already having the same value.

Upvotes: 2

Related Questions