Update Query Problem

(1) By Balaji Ramanathan (balaji) on 2021-02-24 18:59:57 [link] [source]

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
8	1
6	2
5	3
3	4
2	5
1	6

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.

(2) By Dan Kennedy (dan) on 2021-02-24 20:54:03 in reply to 1 [link] [source]

Try leaving out the first instance of "Test" following the FROM clause:

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 WindowOrder WHERE Test.ID = WindowOrder.ID AND Test.Included;

Dan.

(3) By Balaji Ramanathan (balaji) on 2021-02-24 21:23:50 in reply to 2 [link] [source]

Thank you very much, Dan. That worked great!

Is the join clause syntax diagram (https://sqlite.org/syntax/join-clause.html) correct given this syntax for the update statement (https://sqlite.org/syntax/update-stmt.html) using the join clause?

(4.1) By Dan Kennedy (dan) on 2021-02-25 06:34:30 edited from 4.0 in reply to 3 [source]

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.

Dan.

(5) By Balaji Ramanathan (balaji) on 2021-02-25 15:02:36 in reply to 4.1 [link] [source]

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.