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

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.

What is wrong? Can't we have multiple unique ON CONFLICT columns in upsert queries?
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.

CodePudding user response：

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

CodePudding user response：

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

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