KyselyK
Kysely•2y ago
oof2win2

Update on conflict clause

Hi. I need to use the on conflict clause in an update query within sqlite. I've searched the docs in mysql and it isn't present there. I created an example below for replication of the desired behavior. The only thing that I can think of is using the template sql`` operator from kysely, but is there a better way?
CREATE TABLE ReportCategory (
    reportId   TEXT,
    categoryId TEXT,
    PRIMARY KEY (reportId, categoryId)
);

INSERT INTO ReportCategory VALUES
    ("one", "hacking"),
    ("one", "griefing"),
    ("two", "griefing"),
    ("three", "hacking")
;

SELECT * FROM ReportCategory;
-- one|hacking
-- one|griefing
-- two|griefing
-- three|hacking

-- this statement should error
-- UPDATE ReportCategory SET categoryId="hacking" WHERE categoryId="griefing" ON CONFLICT IGNORE;

-- this will work
UPDATE OR IGNORE ReportCategory SET categoryId="hacking" WHERE categoryId="griefing";

SELECT * FROM ReportCategory;
-- one|hacking
-- one|griefing
-- two|hacking
-- three|hacking
Was this page helpful?