SQLite Forum

Update with table alias

Update with table alias

(1) By Juan Luis Paz (juanluispaz) on 2021-10-10 18:25:11 [link]

I'm trying to execute the following query (it is valid in PostgreSQL) with the new returning syntax:

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 ?

(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);
ddevienne=> insert into demo values (1, 'one'), (2, 'two'), (3, 'three');
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)

ddevienne=> select * from demo;
 id |  name
  1 | one
  3 | three
  2 | Name 2
(3 rows)

(4) By Juan Luis Paz (juanluispaz) on 2021-10-11 09:50:30 in reply to 2

Yes, I can omit the table alias or use the table name that I'm updating; but, I cannot use another table alias