Kevin van der Pol Kevin van der Pol - 3 months ago 9
SQL Question

Sqlite Update Select query -- Referencing table being updated in Order By clause of inner Select

I am trying to update values in a table by the result of a select query. The problem is that I am not allowed to reference the outermost table (the table being updated) in the Order By clause of the innermost table (the table to select the new value).

Suppose I have the following table:

MustMatch PreferredMatch Old New
---------- -------------- ---------- ----------
0 Blue Old blue
1 Blue Wrong matc
0 Red Unpreferre
0 Blue Preferred


I would like to fill in the "New" column for "Old blue". The new value needs to be different from the old value, but match on the MustMatch column. The following query will do this:

UPDATE t
SET New = (
SELECT innerTable.Old
FROM t innerTable
WHERE innerTable.Old != t.Old
AND innerTable.MustMatch = t.MustMatch
LIMIT 1
) WHERE Old = "Old blue";

MustMatch PreferredMatch Old New
---------- -------------- ---------- -----------------
0 Blue Old blue Unpreferred match
1 Blue Wrong matc
0 Red Unpreferre
0 Blue Preferred


Now, I want to add a preference: I want to add
ORDER BY innerTable.PreferredMatch = t.PreferredMatch
to the inner query:

UPDATE t
SET New = (
SELECT innerTable.Old
FROM t innerTable
WHERE innerTable.Old != t.Old
AND innerTable.MustMatch = t.MustMatch
ORDER BY innerTable.PreferredMatch = t.PreferredMatch DESC
LIMIT 1
) WHERE Old = "Old blue";


This throws an error
Error: no such column: t.PreferredMatch
.

The problem is the reference to
t
. The Order By clause works as intended when I do

UPDATE t
SET New = (
SELECT innerTable.Old
FROM t innerTable
WHERE innerTable.Old != t.Old
AND innerTable.MustMatch = t.MustMatch
ORDER BY innerTable.PreferredMatch = 'Blue' DESC
LIMIT 1
) WHERE Old = "Old blue";

MustMatch PreferredMatch Old New
---------- -------------- ---------- ---------------
0 Blue Old blue Preferred match
1 Blue Wrong matc
0 Red Unpreferre
0 Blue Preferred


Why am I not allowed to use table
t
in the Order By clause, even though I can use it in the Where clause? Is there another way to achieve this?

Answer

If instead of

UPDATE test 
SET New = (
    SELECT innerTable.Old 
    FROM test innerTable 
    WHERE innerTable.Old != test.Old 
        AND innerTable.MustMatch = test.MustMatch 
    ORDER BY innerTable.PreferredMatch = test.PreferredMatch DESC 
    LIMIT 1
) WHERE Old = "Old blue";

you use one more level

UPDATE test 
SET New = (SELECT newValue FROM
    (SELECT innerTable.Old as newValue,
            innerTable.PreferredMatch = test.PreferredMatch as pick
    FROM test innerTable 
    WHERE innerTable.Old != test.Old 
        AND innerTable.MustMatch = test.MustMatch 
    ORDER BY pick DESC 
    LIMIT 1) foo
) WHERE Old = "Old blue";

then it works (you use in order-by a name of the field in select, and in select you can use a field from the external table which is being updated).

IMHO it's a bug in sqlite - there is no reason why a field from an external table could be used in where and select clauses but not in order-by.