How to combine an existing row with new data in SQLite?

Question

I have a database full of simple note data, with columns for name, date, priority and details. There is also a column _id PRIMARY KEY int.

Let's say I have a note in the table with some data already filled in, and the rest with zeros. I also have a dataset that will populate all of these fields.

Is there a way so that I can only write data to NULL fields?

I cannot overwrite existing data, but I would like to add data to NULL columns.

I know the row row of the target row.

If my target row had a rowId of 5, I could do something like this:

UPDATE SET duedate='some date', priority='2', details='some text' WHERE _id=5

But this will overwrite all the data in this line, and I do not want to lose any data that may be there. How can I change this statement to avoid writing to nonzero fields?

Answer 1

Suppose you start with

CREATE TABLE "t" ("a" , "b" , "c" );
INSERT INTO "t" ("a", "c") VALUES (1, 3);

Then

update t set a = coalesce(a,9), b = coalesce(b,10), c = coalesce(c,11);

Only zero values ​​will be updated, i.e. only column B will be set to 10. A and C will be left alone because they contain values.

Coalesce means selecting the first item in a list that is not null.

Answer 2

The operator UPDATEchanges only the fields specified in the SET clause. If there are fields whose value you want to leave unchanged, simply do not specify these fields in the SET clause.

, UPDATE - , , , WHERE.

, NULL , IFNULL(CurrentValue, NewValueIfNull). .

UPDATE SET due_date=IFNULL(due_date, "some date") ... etc..

, NULL, , NULL, .

. SQL Lite, IFNULL

Answer 3

: UPDATE SET duedate = 'some date' WHERE _id = 5, duedate - null; UPDATE SET priority = '2' WHERE _id = 5, - null; UPDATE SET details = 'some text' WHERE _id = 5, - null;

Mysql, IF() - . , - Oracle ...