How do I update 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 condition for Birthdate.

Thanks in advance

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.