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)