Hi,
Basically: in a subselect in an update statement all fields from the row
which is being updated CAN be used in SELECT and WHERE clauses (see the
first update below using test.whereField in the WHERE clause), but using
them in ORDER BY leads to an error.
Example:
create table test (
whereField integer,
orderField integer,
value integer);
insert into test (whereField, orderField, value)
values
(0, 0, 10),
(1, 2, 30),
(0, 1, 15),
(1, 0, 7);
UPDATE test
SET value = value + 100 *(
SELECT i.value
FROM test i
WHERE i.whereField = test.whereField
LIMIT 1
);
SELECT * FROM test;
-- whereField orderField value
-- ---------- ---------- ----------
-- 0 0 1010
-- 1 2 3030
-- 0 1 101015
-- 1 0 303007
UPDATE test
SET value = value + 100 *(
SELECT i.value
FROM test i
WHERE i.whereField = test.whereField
ORDER BY i.orderField = test.orderField
LIMIT 1
);
Error: no such column: test.orderField
Reproducible with the newest vanilla sqlite:
sqlite-autoconf-3170000.tar.gz
SQLite version 3.17.0 2017-02-13 16:02:40
but the same bug exists in older versions: we noticed it back in September,
see e.g.
http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/
Regards,
Marek
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
