How to merge existing row with new data in SQLite?

Question

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

Say I have a note in the table already with some data filled and the rest null. I also have a set of data that will fill all those fields.

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

I can't overwrite existing data, but I'd like to add data to NULL columns.

I know the rowId of the target row.

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

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

But that would overwrite all the data in that row, and I don't want to lose any data that might be there. How can I change this statement to avoid writing to non-null fields?

Answer 1

The UPDATE statement only changes the fields you specify in the SET clause. If there are fields whose value you want left unmodified, then simply don't specify those fields in the SET clause.

Put another way, UPDATE doesn't write to all fields in the table - just the fields you specify for the rows you select with the WHERE clause.

If you simply don't know if the existing data is NULL or not, you can set the values using IFNULL(CurrentValue, NewValueIfNull). E.g.

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

This will merge your new values into the row where there NULL values, and leave non-NULL values as they were.

See SQL Lite, IFNULL

Answer 2

How about: UPDATE SET duedate='some date' WHERE _id=5 and duedate is null; UPDATE SET priority='2' WHERE _id=5 and priority is null; UPDATE SET details='some text' WHERE _id=5 and priority is null;

If you use Mysql, you can lookup IF()-then you can create a one liner. I think you can do something with similar in Oracle with case...

Answer 3

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

Will update only the null values, ie. only column B will be set to 10. A and C will be left alone because they contain values.

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