Upsert (INSERT ... ON CLONFLICT ... DO UPDATE) failing

PiotrBalas45 0
Viewed 49

My table :

CREATE TABLE "tests" (
"testId"    INTEGER PRIMARY KEY AUTOINCREMENT,
"testUser"  INTEGER UNIQUE,
"testName"  INTEGER UNIQUE
);

Simple upsert works :

INSERT INTO tests (testUser,testName)
VALUES (2,5)
ON CONFLICT (testUser) 
DO UPDATE SET testUser=2, testName=5

But I have two UNIQUE columns and need to trigger UPDATE upon conflict in either testUser or testName. Therefore, I need to check them both in the ON CONFLICT part:

INSERT INTO tests (testUser,testName)
VALUES (2,9)
ON CONFLICT (testUser, testName)
DO UPDATE SET testUser=2, testName=9

Above SQL command fails with:

ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

DBFIDDLE with combinations I tried.

  1. What is wrong? Can't we have multiple unique ON CONFLICT columns in upsert queries?

  2. Is there any other way (apart from REPLACE INTO) to achieve the upsert result?

I could potentially use REPLACE INTO, but have foreign key constrains that cause havoc in another table upon DELETE executed by REPLACE INTO.

sqlite sql
2 Answers

the error is very clear.

with ON CONFLICT (testUser, testName)you need a combined UNIQUE or combined primary key.

like in the sample

CREATE TABLE "tests" (
    "testId"    INTEGER PRIMARY KEY AUTOINCREMENT,
    "testUser"  INTEGER UNIQUE,
    "testName"  INTEGER UNIQUE,
  UNIQUE("testName" ,"testUser")
);
INSERT INTO tests (testUser,testName) 
  VALUES (2,3);
  
  INSERT INTO tests (testUser,testName)
  VALUES (2,9)
  ON CONFLICT (testUser, testName) 
  DO UPDATE SET testUser=2, testName=9
nbk0

Starting with Sqlite version 3.35.0 you can have multiple ON CONFLICT clauses and a DO UPDATE resolution without a conflict target in SQLite version 3.35.0 (2021-03-12). So you can either write your command as:

INSERT INTO tests (testUser,testName)
VALUES (2,9)
ON CONFLICT (testUser)
DO UPDATE SET testUser=2, testName=9
ON CONFLICT (testName)
DO UPDATE SET testUser=2, testName=9

or

INSERT INTO tests (testUser,testName)
VALUES (2,9)
ON CONFLICT
DO UPDATE SET testUser=2, testName=9

Note that to test it in DBFIDDLE you need to select a sqlite version greater than 3.35.0

Andrea B.0
Related