CODEWITHSUNDEEP
sqlitesql-update
Peri Hartman
Peri Hartman

Reputation: 19474

in sqlite, how to update a column to null conditionally

In Sqlite, how can I update a varchar column to null if the value has length 0, otherwise to the value.

In other words, something like this, using C-like syntax:

update T set C = (value.length() == 0 ? null : value) where ...;

Thanks.

Upvotes: 2

Views: 6788

Answers (1)

Helix 88
Helix 88

Reputation: 701

Provided that the table schema has been defined to allow null values in the column then the following query should produce the results that you are looking for

UPDATE T
SET ColumnNameX = null
WHERE LENGTH(ColumnNameX) = 0

This could be enforced using a Trigger.

CREATE TRIGGER TNullTrigger
AFTER INSERT ON T 
FOR EACH ROW
BEGIN
  UPDATE T
  SET ColumnNameX = null
  WHERE LENGTH(ColumnNameX) = 0
END;

Upvotes: 4

Related Questions