UPDATE x AS y: alias invalid in "SET" expr
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
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.
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.
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.
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.