Search code examples
sqlsqliteupsert

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

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.

Solution

  • 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)