I am having a problem with getting an update query to run without syntax errors.

Consider a table that is created and populated using the code below:

DROP Table if exists Test;

create temp table Test(ID INTEGER, Included BOOLEAN, SelectOrder INTEGER);

insert into Test VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 0, 4), (5, 1, 5), (6, 1, 6), (7, 0, 7), (8, 1, 8), (9, 0, 9);

select * from Test;

Now, I want to update the SelectOrder column: In particular, I want to update it for the rows in which Included is True, and I want the SelectOrder to be in reverse order of ID (so, the row with ID 8 would get SelectOrder 1, and the row with ID 1 would get SelectOrder 6).

I tried the update query below:

WITH WindowOrder AS ( SELECT ID, row_number() OVER (ORDER BY ID desc) AS RowNumber FROM Test WHERE Included UPDATE Test SET SelectOrder = WindowOrder.RowNumber FROM Test INNER JOIN WindowOrder ON Test.ID = WindowOrder.ID WHERE Test.Included;

It fails with the error "Error: near line 18: ambiguous column name: Test.Included".

The subquery does work and produces the output below:

ID RowNumber

And as far as I can tell, the update query syntax is correct. The error message indicates to me that something else may be going on (and the error message is not helpful). It also doesn't seem to matter whether the Test table is a temp table or not (I tried with and without the keyword "temp").

What am I doing wrong? Thank you.

UPDATE Test SET SelectOrder = WindowOrder.RowNumber FROM WindowOrder WHERE Test.ID = WindowOrder.ID AND Test.Included;

I think so.

SQLite joins the target table against the tables in the FROM clause of an UPDATE FROM statement. So your first attempt was similar to:

    SELECT _rowid_, ... FROM Test, Test INNER JOIN WindowOrder ON ...
Hence any reference to a Test.* column was ambiguous.
From memory, this is different to the way SQL Server does it. I think your statement (with the target table repeated in the FROM clause) would be correct for SQL Server. Not 100% sure about that though.
OK, that may be so.  I could not create the syntax for SQLite from the syntax diagram (and that may be a problem with how I was looking at the syntax diagram, but an example may be helpful on that page).  I got the syntax that I did use from StackOverflow, so it may have been specific to SQL Server or something like that.