How do I update sql Column with NULL Values-CodePudding

I have two tables

Tb1

ID  Name  Birthdate
1   Ball  NULL
2   Tire  2022-01-01
3   Ball  1900-01-01

Tb2

ID     Name  Birthdate
NULL   Ball  NULL
NULL   Tire  2022-01-01
NULL   Ball  1900-01-01

I want to Update Tb2.ID to follow Tb1.ID, so here is my script

update Tb2.ID = Tb1.ID
from Tb2
LEFT JOIN Tb1
ON Tb2.Name = Tb1.Name
and ISNULL(Tb2.Birthdate,'') = ISNULL(Tb1.Birthdate,'')
where Tb2.ID is NULL

And the result is ID 1 has 2 rows in Tb2 as seen like this table

ID	Name	Birthdate
1	Ball	NULL
2	Tire	2022-01-01
1	Ball	1900-01-01

My expectation is ID 3 is updated to Tb2 for this record

ID   Name   Birthdate
3    Ball   1900-01-01

Can you guys help? I suspect in the script goes wrong in Join key for Birthdate

Thanks before

CodePudding user response：

UPDATE Tb2
SET Tb2.ID = Tb1.ID
FROM Tb2
LEFT JOIN Tb1
ON Tb2.Name = Tb1.Name
AND ISNULL(Tb2.Birthdate,'') = ISNULL(Tb1.Birthdate,'')
WHERE Tb2.ID IS NULL

This will update the ID column in Tb2 with the corresponding ID values from Tb1 for all rows where Tb2.ID is NULL.

The resulting table would look like this:

ID Name Birthdate 1 Ball NULL 2 Tire 2022-01-01 3 Ball 1900-01-01

I hope this helps! Let me know if you have any questions.

CodePudding user response：

Try:

UPDATE Tb2
SET Tb2.ID = Tb1.ID
FROM Tb2
LEFT JOIN Tb1
ON Tb2.Name = Tb1.Name
AND ISNULL(Tb2.Birthdate,'9999-12-31') = ISNULL(Tb1.Birthdate,'9999-12-31')
WHERE Tb2.ID IS NULL

I think the empty strings convert to 1900-01-01 in your ISNULLs, using an invalid (but not 1900-01-01) date for NULL birth dates would address that.

CodePudding user response：

If you are using SQL Server 2022 (or later), you can also use the new IS [NOT] DISTINCT FROM syntax. This allows for comparing both null and non-null values.

UPDATE Tb2
SET ID = Tb1.ID
FROM Tb2
JOIN Tb1
    ON Tb2.Name = Tb1.Name
    AND Tb2.Birthdate IS NOT DISTINCT FROM Tb1.Birthdate
WHERE Tb2.ID IS NULL

Note that there is no need for a left join, since you really only want to update when you have a match. Use an inner join instead.

See also this db<>fiddle for a quick DISTINCT FROM demo.