Update or add value with id

Question

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 1

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.

Answer 2

You are already there, try something like this:

Check if the record exists. If it does, then update otherwise add a new row. Using named arguments as per documentation would be ideal. https://cashapp.github.io/sqldelight/native_sqlite/query_arguments/

updateOrInsert:
IF EXISTS (SELECT 1 FROM Color WHERE id = :id) 
  BEGIN
    UPDATE Color
    SET id = :id, 
        name = :name, 
        hex = :hex
    WHERE id = :id 
  END
ELSE
  BEGIN
    INSERT INTO Color(id, name, hex)
    VALUES (:id, :name, :hex);
  END

Usage

dbQuery.updateOrInsert(id = BGColor.id, name = bgColor.name, hex = bgColor.hex)

Take a look at this, might be useful REPLACE INTO vs Update