Using SQLite 3.6.21, I would like to update a column in a table.
The goal is to "squeeze out" NULLs from a column if there is only 1 unique real value in that column. If LastName contained "Smith", "Johnson", and a NULL, then do nothing.
For example:
create table foo (FirstName char(20), LastName char(20));
insert into foo values ('Joe', 'Smith');
insert into foo values ('Susan', NULL);
insert into foo values ('Shirley', 'Smith');
insert into foo values ('Kevin', NULL);
Since there is only one last name, I want to replace the NULLs with Smith. I have tried this without success. It ends up replacing the whole column with NULLs.
UPDATE foo
SET LastName =
( CASE
WHEN ((select count(distinct LastName) from foo) = 1) THEN (SELECT distinct LastName from foo)
ELSE LastName
END
);
Aucun commentaire:
Enregistrer un commentaire