I made a patch to allow an alias for target table in UPDATE/DELETE
This is a TODO item.
> o Allow an alias to be provided for the target table in UPDATE/DELETE
> This is not SQL-spec but many DBMSs allow it.
Example:
UPDATE accounts AS a SET a.abalance = a.abalance + 10 WHERE a.aid = 1;
I think that there are two viewpoints of this patch:
(1)I moved SET to reserved words to avoid shift/reduce conflicts.
It is because the parser confused by whether SET is a keyword or
an alias in SQL 'UPDATE tbl SET ...'.
(2)About processing when column identifier of SET clause is specified
like 'AAA.BBB'. 'AAA' is a composite column now. When an alias for
target table is supported, 'AAA' is a composite column or a table.
How do we distinguish these?
The syntax rule of the composite type is as follows:
-----------------------------------------------------------------------
SELECT item.name FROM on_hand WHERE item.price > 9.99;
This will not work since the name item is taken to be a table name,
not a field name, per SQL syntax rules. You must write it like this:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
-----------------------------------------------------------------------
but...
-----------------------------------------------------------------------
We can update an individual subfield of a composite column:
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
Notice here that we don't need to (and indeed cannot) put parentheses
around the column name appearing just after SET, but we do need
parentheses when referencing the same column in the expression to
the right of the equal sign.
-----------------------------------------------------------------------
The syntax rule is different in SET clause and other clauses.
Incompatibility is caused if SET clause is changed to the same
rule as other clauses to allow an alias for target table.
To keep compatibility, I implemented the following rules:
Eat up a first element of column identifier when the first element
is a name or an alias for target table. And, make the second
element a column name.
Example:
UPDATE tbl AS t SET t.col = 1;
=> 't' is eaten up, and 'col' becomes a column name.
--- Atsushi Ogawa
