SQLite Forum

UPDATE x AS y: alias invalid in "SET" expr
Login

UPDATE x AS y: alias invalid in "SET" expr

(1) By Mark Lawrence (mark) on 2020-09-01 09:57:52 [link] [source]

The following fails to parse:

CREATE TABLE atable(id INTEGER);

UPDATE atable AS a
SET a.id = 2  -- HERE
WHERE a.id = 1;

The "a.id" is not accepted in a SET expression even though it works in a WHERE expression. For consistency it would be nice if it did so. Has only become interesting to me since the UPDATE ... FROM support was added - I wanted to qualify all column names.

Version 3.34.0 2020-09-01 00:26:21 3ca0b7d54d73d07cd6b32e650a809174bb1cd66ce5ecdb36f65b70899ea05824

(2) By Richard Hipp (drh) on 2020-09-01 11:39:33 in reply to 1 [link] [source]

The ability to put table-name qualifiers in front of the column names in the SET clause of an UPDATE statement is a MySQL-ism. I'm not aware of any SQL engine other than MySQL that supports that syntax.

(3) By AAsk (aa2e72e) on 2020-09-01 13:51:43 in reply to 2 [source]

SQL Server supports it.

select 1 as id, 100 as value into #tmp update a set a.value = 2000 from #tmp a where a.id = 1;

Useful when you update a table using an inner or left join with another table and the new values come from the second table.

(4) By AAsk (aa2e72e) on 2020-09-01 13:54:39 in reply to 3 [link] [source]

The sql statements got joined - it should be

select 1 as id, 100 as value into #tmp;

update a set a.value = 2000 from #tmp a where a.id = 1;

(5.1) By Keith Medcalf (kmedcalf) on 2020-09-01 15:21:13 edited from 5.0 in reply to 4 [link] [source]

That is a Sybase-ism. SQLite3 is not Sybase.

The tablename specified after the UPDATE keyword is included in the query processing and is not repeated in the FROM clause unless you want to have the same table included twice.

That is, in Sybase, when you have a FROM clause in an UPDATE statement then one of the tables in that FROM clause must follow the UPDATE keyword in order to specify which particular table of the join is being updated. This is a hold-over from the original SQL in which one spoke:

SELECT * FROM a,b ... FOR UPDATE OF a;

as the SQL statement and then proceeded to do an

UPDATE A SET col=value WHERE CURRENT OF CURSOR

as the select ran.

Sybase contracted this to:

UPDATE a set col=value FROM a,b ...;

and Microsoft stole Sybase 4 and renamed it SQL Server.

(6) By anonymous on 2020-09-01 16:19:47 in reply to 1 [link] [source]

I second this. Microsoft Access allows this as well and I was initially confused to learn this was not supported. Access allows performing joins in the table_specifier prior to the set_clause which makes table aliases far more useful as you can perform updates across multiple tables with a single query.