SQLite UPSERT, no such column: excluded.<fieldname>-CodePudding

I'm trying to use the SQLIte Upsert special syntax. On the documentation site, https://www.sqlite.org/lang_UPSERT.html, it shows an example like this.

INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

the documentation explains that the 'excluded.' prefix is necessary to use the value that would have been inserted.

The query I've created is this,

INSERT INTO Computers (Name,Model,SerialNumber) 
SELECT ComputerName, Model, SerialNumber
FROM DataImport 
WHERE true 
ON CONFLICT(SerialNumber) DO UPDATE SET Name=excluded.ComputerName 
WHERE length(excluded.ComputerName) > length(Name)

the result I receive is Result: no such column: excluded.ComputerName. I can't find any resolution to this. I know I could just do two separate Insert and Update statements but I'd rather find the resolution to this because I'd like to apply this approach to more complex tasks in the future.

CodePudding user response：

excluded has the column names from Computers, not from Dataimport

INSERT INTO Computers (Name,Model,SerialNumber) 
SELECT ComputerName, Model, SerialNumber
FROM DataImport 
WHERE true 
ON CONFLICT(SerialNumber) DO UPDATE SET Name=excluded.Name 
WHERE length(excluded.Name) > length(Computers.Name)

CodePudding user response：

Here's your problem:

WHERE length(excluded.ComputerName)

The excluded prefix is just for the ON CONFLICT clause. But the column doesn't get that prefix anywhere else in the query. Change it to just:

WHERE length(ComputerName)

and you should be fine.