Update NULL values in column with known values SQL

Question

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

What is the purpose of SELECT name, max(nationality) FROM t1 ? — Dai
– Dai, Commented Aug 8, 2022 at 18:51
Don't you just need UPDATE t1 SET nationality = CASE name WHEN 'BBB' THEN 'english' WHEN 'AAA' THEN 'french' ELSE nationality END WHERE t1.nationality IS NULL AND name IN ( 'AAA', 'BBB' ) ? — Dai
– Dai, Commented Aug 8, 2022 at 18:52

forpas · Accepted Answer · 2022-08-08 19:02:31Z

1

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.

answered Aug 8, 2022 at 19:02

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andy Lester · Accepted Answer · 2022-08-08 18:54:25Z

0

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

answered Aug 8, 2022 at 18:54

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

Collectives™ on Stack Overflow

Update NULL values in column with known values SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related