Update with table alias
(1) By Juan Luis Paz (juanluispaz) on 2021-10-10 18:25:11
I'm trying to execute the following query (it is valid in PostgreSQL) with the new returning syntax: ```sql update demo as _new_ set name = 'Name 2' from (select * from demo as _old_ where _old_.id = 3) as _old_ where _new_.id = _old_.id returning _old_.name as oldName, _new_.name as newName ``` And I got the following error message: ``` no such column: _old_.name ``` Table creation: ``` create table demo(id number, name text) ``` It is like no table alias is valid in the returning clause. As far there is a table alias, there will be an error. Tested on sqlite 3.36.0
(2) By Simon Slavin (slavin) on 2021-10-11 01:52:30 in reply to 1 [link]
The RETURNING clause apparently doesn't have access to internal aliases. If you change <pre>_old_.name</pre> to <pre>demo.name</pre> does it work ?
(4) By Juan Luis Paz (juanluispaz) on 2021-10-11 09:50:30 in reply to 2 [link]
Yes, I can omit the table alias or use the table name that I'm updating; but, I cannot use another table alias
(3) By ddevienne on 2021-10-11 06:49:21 in reply to 1 [link]
Given that this works on PostgreSQL (see below), and SQLite strives for compatibility with it, I suspect this could be considered an actual issue. Lets see what Richard thinks of this. ``` D:\trunk> psql -h pq Password for user ddevienne: psql (12.1, server 12.5) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. ddevienne=> create table demo (id serial, name text); CREATE TABLE ddevienne=> insert into demo values (1, 'one'), (2, 'two'), (3, 'three'); INSERT 0 3 ddevienne=> update demo as _new_ set name = 'Name 2' ddevienne-> from (select * from demo as _old_ where _old_.id = 2) as _old_ ddevienne-> where _new_.id = _old_.id ddevienne-> returning _old_.name as oldName, _new_.name as newName; oldname | newname ---------+--------- two | Name 2 (1 row) UPDATE 1 ddevienne=> select * from demo; id | name ----+-------- 1 | one 3 | three 2 | Name 2 (3 rows) ```