Best way to update NULL in records

user_6102713 0
Viewed 36

I have a table say TA where the data looks like,

ID SSN Date
Null 123 1
Null 123 2
Null 123 3
xyzabc 123 4

I want to get one value of ID that is not null and update all the other rows based on that value.

What I have come up with:

UPDATE TA 
SET ID = (SELECT DISTINCT ID FROM TA as T1 
          WHERE TA.SSN= T1.SSN AND T1.ID !=NULL)
          WHERE TA.ID=NULL;

I wanted to know the most efficient way to do this as I might be performing this on a table with a large number of rows.

sql
1 Answers

When you need compare with NULL you must use IS NULL / IS NOT NULL statements

UPDATE TA 
JOIN (
    SELECT MIN(ID) ID, SSN FROM TA GROUP BY SSN
) TA1 ON TA.SSN = TA1.SSN
SET TA.ID = TA1.ID
WHERE TA.ID IS NULL;

SQL editor online

The above example for MySQL, when you have another server you need to match the query

user_33993560
Related