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
allow_alias_update.patch
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster