CODEWITHSUNDEEP
sqlsqlite
Greg mcGilles
Greg mcGilles

Reputation: 1

Only replace column

What would be the best way to replace an entire row in a table with values, but only replace a specific value if the one we have is not NULL.

To elaborate, I currently use this query: 

INSERT OR REPLACE INTO items (name, quantity, last_user) VALUES ('test', '3', 'John');

Now what I am trying to do is run the above query, and only replace the last_user if the value specified is not NULL. The rest I would like to replace anyway.

So ideally, if the last user is now NULL, keep the last field. I could of course do a SELECT statement to grab the last known value, check if the new one is NULL and if it is insert the last known value instead - but I would like to do this using a single query.

Upvotes: 0

Views: 48

Answers (1)

Samuel Neff
Samuel Neff

Reputation: 74909

Use an update statement with COALESCE, passing the old and new values. This will allow you to keep the existing value if it's not null, and replace it if it is null.

UPDATE MyTable 
SET
    name = 'new-name',
    value = COALESCE(value, 'possible-new-value')
WHERE

    id = 1;

Upvotes: 2

Related Questions