CODEWITHSUNDEEP
sqlsqlitesql-updatewhere-clausesql-null
HJA24
HJA24

Reputation: 412

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

Upvotes: 1

Views: 60

Answers (2)

forpas
forpas

Reputation: 164089

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.

Upvotes: 1

Andy Lester
Andy Lester

Reputation: 93636

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';

Upvotes: 0

Related Questions