CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-update
AToya
AToya

Reputation: 575

Check Differences between two Tables with allowed nulls in SQL

I have two different tables, Table1 & Table2 each with their own sets of values. I want to check a column to see if there are any differences from each other and UPDATE Table1 accordingly. I have this query that updates every row regardless if they differ in the value I'm checking for:

UPDATE Table1
SET value = t2.value
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ID = t2.ID

I tried using WHERE t1.value <> t2.value but since either t1 and t2 can be null, the function does not work properly. I want a query that only checks and updates where their values t1 and t2 are different.

Upvotes: 0

Views: 131

Answers (4)

David Nhim
David Nhim

Reputation: 469

How about using coalesce to provide a default value for your nulls? Change your where clause like so. 

WHERE COALESCE(t1.value,'') <> COALESCE(t2.value,'')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If I understand the question correctly, you can list the conditions where you want to do the update in a where clause:

UPDATE Table1
    SET value = t2.value
    FROM Table1 t1 INNER JOIN
         Table2 t2
         ON t1.ID = t2.ID
   WHERE (table1.value <> t2.value) or
         (table1.value is null and table2.value is not null) or
         (table1.value is not null and table2.value is null)

Upvotes: 1

anon
anon

Reputation: 

...
WHERE t1.value <> t2.value
OR (t1.value IS NULL AND t2.value IS NOT NULL)
OR (t1.value IS NOT NULL AND t2.value IS NULL);

Upvotes: 2

user2640118
user2640118

Reputation: 31

You could just tack on where t1.id is not null and t2.ID is not null

eg:

UPDATE Table1
SET value = t2.value
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ID = t2.ID
WHERE t1.value <> t2.value
AND t1.id is not null 
AND t2.ID is not null

Upvotes: 1

Related Questions