CODEWITHSUNDEEP
sqlsql-server
Phh
Phh

Reputation: 135

how to update row = NULL when there is no match

i have 2 Tables like this:

CREATE TABLE #targetTable(id int,date datetime,name varchar(50));
CREATE TABLE #sourceTable(id int,date datetime,name varchar(50));
INSERT INTO #targetTable  values(1,'1905-07-08 00:00:00.000','John');
INSERT INTO #targetTable values(2,'1905-07-08 00:00:00.000','Albrt');
INSERT INTO #targetTable  values(3,'1905-07-08 00:00:00.000','Roy');

INSERT INTO #sourceTable values(1,'1905-07-09 00:00:00.000','jame');

i want to update the Target Table, when not match then update Name Column of Target with NULL. i would like to have this Result:

id   date                     name
1   1905-07-09 00:00:00.000    jame
2   1905-07-09 00:00:00.000    null
3   1905-07-09 00:00:00.000    null

my test Query doesn't work: Error: UPDATE IS NOT ALLOWED IN THE WHEN NOT MATCHED. is there anyway to edit the query to get the Results?

merge into #targetTable a 
using #sourceTable b on a.id=b.id 
when matched and b.date > a.date then
   update 
   set a.name=b.name,a.date=b.date 
when not matched by Target then 
   update a.date=b.date and a.name = null

it shows me Error. Can you please help me how to get the Result?

Upvotes: 2

Views: 1797

Answers (5)

SchmitzIT
SchmitzIT

Reputation: 9552

So if I get your problem, you simply want to update the name in table #targetTable if there is no matching row in #sourceTable.

You don't need a MERGE statement to accomplish this.

UPDATE #targetTable
SET 
    [name] = COALESCE([source].name, NULL)
    , date = [source_date].maxDate
FROM 
    #targetTable AS [target]
    LEFT JOIN #sourceTable AS [source] ON [target].id = [source].id
    CROSS JOIN (SELECT max(date) AS maxDate FROM #sourcetable) AS [source_date] 
WHERE 
    [source].id IS NULL

This yields the following output:

enter image description here

Upvotes: 0

Mark Schultheiss
Mark Schultheiss

Reputation: 34158

After reading the question and all the comments what you really want is

  • UPDATE the row based upon the id with both the date and name but set the name to NULL when there is no match and set the date to the max date at the same time with that condition.
  • UPDATE with new values (name,date) when there is a match on id

I used a table variable for simplicity here but the concept is the same.

Given the table and data this produces:

DECLARE  @targetTable TABLE (id int,date datetime,name varchar(50));
DECLARE  @sourceTable TABLE (id int,date datetime,name varchar(50));
INSERT INTO @targetTable  
VALUES
(1,'1905-07-08 00:00:00.000','John'),
(2,'1905-07-08 00:00:00.000','Albrt'),
(3,'1905-07-08 00:00:00.000','Roy');

INSERT INTO @sourceTable values(1,'1905-07-09 00:00:00.000','jame');

SELECT  id,date,name  FROM @targetTable;
SELECT  id,date,name  FROM @sourceTable;

id  date    name
1   1905-07-08 00:00:00.000 John
2   1905-07-08 00:00:00.000 Albrt
3   1905-07-08 00:00:00.000 Roy

id  date    name
1   1905-07-09 00:00:00.000 jame

What you desire is basically the same as this select statement:

SELECT 
    t.id,
    CASE 
        WHEN s.id IS NOT NULL THEN s.[date]
        ELSE (SELECT MAX([date]) FROM @sourceTable )
    END AS [date],
    CASE 
        WHEN s.id IS NULL THEN NULL
        ELSE s.name
    END AS [name]
FROM @targetTable AS t
LEFT OUTER JOIN  @sourceTable AS s
    ON t.id = s.id;

SO to do that, we can incorporate that into an update:

UPDATE @targetTable
    SET [date] = CASE 
        WHEN s.id IS NOT NULL THEN s.[date]
        ELSE (SELECT MAX([date]) FROM @sourceTable )
    END,
    [name] = CASE 
        WHEN s.id IS NULL THEN NULL
        ELSE s.name
    END
FROM @targetTable AS t
LEFT OUTER JOIN  @sourceTable AS s
    ON t.id = s.id;

Final output 

SELECT id,date,name  FROM @targetTable;

id  date                    name
1   1905-07-09 00:00:00.000 jame
2   1905-07-09 00:00:00.000 NULL
3   1905-07-09 00:00:00.000 NULL

Upvotes: 0

Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

No offense but your query has whole a lot of syntax issues.

Secondly, a merge statement cannot update values in the target table when not matched. You should try inserting instead.

Here is a example: 

MERGE INTO #targetTable a 
USING #sourceTable b 
ON a.id=b.id 
WHEN MATCHED THEN 
    UPDATE SET 
        a.name=b.name,
        a.date=b.date 
WHEN NOT MATCHED BY TARGET THEN 
    INSERT  
    (
        id,
        date,
        name
    )
    VALUES 
    (
        b.id,
        b.date,
        null as name
    )

Upvotes: 4

MarcinJ
MarcinJ

Reputation: 3629

You need WHEN NOT MATCHED BY SOURCE, so something like:

WHEN NOT MATCHED BY SOURCE
THEN UPDATE 
        SET a.name = NULL

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use APPLY : 

UPDATE t
       SET t.name = s.name, 
           t.date = s.date
FROM #targetTable t OUTER APPLY
     ( SELECT TOP (1) s.*
       FROM #sourceTable s
       WHERE s.id = t.id
     ) s;

Your merge has Source value also :

merge #targetTable a 
using #sourceTable b 
      on a.id=b.id 
when matched  
     then update 
         set a.name = b.name, a.date= b.date 
when not matched by SOURCE  
     then update 
          set a.name = null;

Upvotes: 0

Related Questions