CODEWITHSUNDEEP
sql-server-2008t-sql
jidl
jidl

Reputation: 295

Using UPDATE with MERGE WHEN NOT MATCHED

If I'm updating from a query like below. I can wrap the t2.co1 in a COALESCE function to provide an alternative value for rows not found in the source query.

    UPDATE
        t1
    SET
        t1.col1 = COALESCE(t2.col1,0)
    FROM
        table AS t1
    LEFT JOIN
        other_table AS t2
    ON
        t1.id = t2.id

I recently discovered the MERGE statement - but then found that you can't do an UPDATE on WHEN NOT MATCHED clauses. Is there a way to do this with MERGE or should I stick to using above?

Upvotes: 1

Views: 20091

Answers (2)

Chetan Hirapara
Chetan Hirapara

Reputation: 694

Simple example of merge in SQL SERVER

CREATE TABLE #targetTable(id int,name varchar(50))
CREATE TABLE #sourceTable(id int,name varchar(50))

INSERT INTO #sourceTable values(1,'John');
INSERT INTO #sourceTable values(1,'Albrt');
INSERT INTO #sourceTable values(1,'Roy');

MERGE #targetTable AS [target]
USING #sourceTable AS [source]
ON [target].id = [source].id
WHEN NOT MATCHED THEN
INSERT (id, Name)
VALUES (source.id, source.Name);

select * from #targetTable as T
drop table #targetTable
drop table #sourceTable

Upvotes: -3

Serg
Serg

Reputation: 2427

As for me I like to use MERGE, but I agree that it has no sense for your particular simple case. But if we take into consideration that this is simplified example we can solve this task with MERGE. In your case you need to set value of col1 to 0 in target table (@table) when it has no match in source table (@other_table). Here we are:

-- Setting up test tables and data
DECLARE @table TABLE (
     id     INT
    ,col1   INT
)
INSERT INTO @table (id, col1) VALUES (1, 101)
INSERT INTO @table (id, col1) VALUES (2, 102)
INSERT INTO @table (id, col1) VALUES (3, 103)
INSERT INTO @table (id, col1) VALUES (4, 104)

-- Target table before update
SELECT * FROM @table

DECLARE @other_table TABLE (
     id     INT
    ,col1   INT
)
INSERT INTO @other_table (id, col1) VALUES (1, 201)
INSERT INTO @other_table (id, col1) VALUES (2, 202)
INSERT INTO @other_table (id, col1) VALUES (3, 203)

-- Merging target and source tables
MERGE INTO @table AS t1
   USING @other_table AS t2
        ON  t1.id = t2.id

WHEN MATCHED
THEN
   UPDATE
      SET   col1 = t2.col1

WHEN NOT MATCHED BY SOURCE
THEN
   UPDATE
      SET   col1 = 0
;

-- Target table after update using merge
SELECT * FROM @table

Resume - use MERGE when you really need merge, use UPDATE when you need only update.

Upvotes: 4

Related Questions