Kysely•6mo ago

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

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

2 Replies

AlexErrant•5mo ago

insert works for me on sqlite, what does your update stmnt look like https://github.com/AlexErrant/Pentive/blob/0d3dc078952176a4cdbdeeb45253719f191eea43/app/src/sqlite/card.ts#L404-L410

GitHub

Pentive/app/src/sqlite/card.ts at 0d3dc078952176a4cdbdeeb45253719f1...

Collaborative Spaced Repetition. Contribute to AlexErrant/Pentive development by creating an account on GitHub.

oof2win2•5mo ago

yeah but i want update on conflict ignore not insert on conflict ignore

Gaming

Programming

Update on conflict clause