vendredi 17 juillet 2015

SQLite 3.6.21: "Squeezing" out NULL values with a unique column value

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