Update or add value with id

I am just learning SQLDelight and was hoping to find how to add an object with an ID if it doesn’t exist already if it does exist then update that current object with the given id.

currently, I am deleting the current object with id, then adding an object and was hoping to reduce this into one simple call.

My current code:

CREATE TABLE Color (
  id TEXT NOT NULL,
  name TEXT NOT NULL,
  hex TEXT NOT NULL
);

getColorWithId:
SELECT * FROM Color
WHERE id = ?;

saveColor:
INSERT OR REPLACE INTO Color (id, name, hex)
VALUES (?, ?, ?);

deleteColorWithId:
DELETE FROM Color
WHERE id = ?;

I was hoping to change it to replace saveColor and deleteColorWithId with something like:

updateColorWithId:
INSERT OR REPLACE INTO Color (id, name, hex)
WHERE id = ?
VALUES (?, ?, ?);

but it doesn’t work with this error <insert stmt values real> expected, got 'WHERE'

can anyone help? I can’t find anything in the docs.

Answer

Your statement saveColor serves as UPSERT command and it works exactly as you wish. You don’t need to create another statement.

INSERT OR REPLACE INTO Color (id, name, hex)
VALUES (?, ?, ?);

You must specify PRIMARY KEY on id column and you can use saveColor as updateColorWithId.

Advertisement

Answer