sqlsqlitesql-updatewhere-clausesql-null

Update NULL values in column with known values SQL


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


Solution

  • 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.