Update a target table only when source data are not null and different

Question

I have 2 tables with the same columns. The first table Temp1 is loaded with a sp and is temporary. It is used to load a table T1 on a production environment. By example, I have this data :

Table Temp1 


Id  Col1    Col2    Col3    Col4    Col5
2   null    null    0.5     null    0.6
3   0.1     null    null    null    null


Table T1 

Id  Col1    Col2    Col3    Col4    Col5
1   2        3       0.4       5    0.6
2   5        4         6       4    7
3   8        9         7       10   1

I need to update T1 with data from Temp1 only when columns from Temp1 are not null and have not the same value in T1 (bold values in my example).

I’m stuck with this problem. Any ideas please ?

Thanks,

Use an update statement with a join to join the two tables and a case expression for each column. — Sean Lange, Apr 19 '17 at 20:20
Can you please elaborate on your answer and explain how he can have a join with an update statement? — TheEsnSiavashi, Apr 19 '17 at 20:27
http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server — Isaac, Apr 19 '17 at 20:29
Possible duplicate of [How to UPDATE from a SELECT in SQL Server?](http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server) — Sean Lange, Apr 19 '17 at 20:33

score 2 · Accepted Answer · answered Apr 19 '17 at 20:24

2

Using an update statement with a case expression would be something like this.

update tt1
    set Col1 = case when T.Col1 is not null and T.Col1 <> tt1.Col1 then T.Col1 else tt1.Col1 end
from T1 tt1
join Temp1 t on t.Id = tt1.Id

answered Apr 19 '17 at 20:24

Sean Lange

33,028
3
25
40

It's so obvious... Thank you very much ! you save my day :) – user3463579 Apr 30 '17 at 13:48

score 1 · Answer 2 · answered Apr 19 '17 at 20:30

UPDATE T1
  SET T1.Col1 = ISNULL(NULLIF(ISNULL(tmp.Col1 , T1.Col1) , T1.Col1) , T1.Col1)
   ,  T1.Col2 = ISNULL(NULLIF(ISNULL(tmp.Col2 , T1.Col2) , T1.Col2) , T1.Col2)
   ,  T1.Col3 = ISNULL(NULLIF(ISNULL(tmp.Col3 , T1.Col3) , T1.Col3) , T1.Col3)
   ,  T1.Col4 = ISNULL(NULLIF(ISNULL(tmp.Col4 , T1.Col4) , T1.Col4) , T1.Col4)
   ,  T1.Col5 = ISNULL(NULLIF(ISNULL(tmp.Col5 , T1.Col5) , T1.Col5) , T1.Col5)
FROM Temp1 tmp
INNER JOIN T1   ON t1.ID = tmp.ID

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

you can use a merge statement for this problem.

CREATE TABLE #t
    (
        ID int IDENTITY(1,1),
        Name NVARCHAR(50),
        Salary DECIMAL(10,2)
    )

INSERT INTO #t (Name, Salary) VALUES('abc', 123.4)
INSERT INTO #t (Name, Salary) VALUES('pqa', 127.4)
INSERT INTO #t (Name, Salary) VALUES('xyz', 233.4)


CREATE TABLE #tempForT
(
    ID int IDENTITY(1,1),
    Name NVARCHAR(50),
    Salary DECIMAL(10,2)
)

INSERT INTO #tempForT (Name, Salary) VALUES('abc', 200.4)
INSERT INTO #tempForT (Name, Salary) VALUES('pqa', 200.4)
INSERT INTO #tempForT (Name, Salary) VALUES('xyz', NULL)

SELECT * FROM #t
SELECT * FROM #tempForT

Here is the Solution

MERGE #t AS DestTable
USING (
        SELECT 
            NAME,
            Salary
        FROM #tempForT
    )SourceTable
    ON DestTable.Name = SourceTable.Name        --Should contain columns like composite primary key
    --AND other columns
WHEN MATCHED
    THEN
        UPDATE 
            SET DestTable.Salary = ISNULL(SourceTable.Salary, DestTable.Salary) --checking for NULL
        ; 

SELECT * FROM #t

Note: If #tempForT contains new values (rows), then those rows will not get inserted for that you have to update the merge statement with WHEN NOT MATCHED in that case insert new records.

Update a target table only when source data are not null and different

3 Answers3