Update NULL values in column with known values SQL-CodePudding

If I have the following table

name	nationality
AAA	french
BBB	english
CCC	spanish
DDD	dutch
BBB	`NULL`
AAA	`NULL`

How do I update the NULL values with 'english' and 'french' respectively

I tried the following but it doesn't work:

UPDATE
    t1
SET
    t1.nationality = known.Nationality
FROM
    t1
    LEFT JOIN (
        SELECT name, max(nationality) FROM t1
    ) AS known
        ON t1.name = known.name

Edit

In the end there are more cases of NULL values for other names

Thanks in advance

CodePudding user response：

This is the correct UPDATE...FROM syntax for SQLite 3.33.0 :

UPDATE tablename AS t1
SET nationality = t2.nationality
FROM (SELECT name, MAX(nationality) nationality FROM tablename GROUP BY name) AS t2
WHERE t1.name = t2.name AND t1.nationality IS NULL;

See the demo.

For previous versions use a correlated subquery:

UPDATE tablename AS t1
SET nationality = (SELECT MAX(t2.nationality) FROM tablename t2 WHERE t2.name = t1.name)
WHERE t1.nationality IS NULL;

See the demo.

CodePudding user response：

Just call UPDATE:

UPDATE t1 SET nationality = 'english' WHERE nationality IS NULL AND name = 'AAA';
UPDATE t1 SET nationality = 'french' WHERE nationality IS NULL AND name = 'BBB';