Posted by --CELKO-- on 05/11/07 18:03

The ANSI model of an UPDATE is that it acts as if

1) You go to the base table. It cannot have an alias because an alias
would create a working table that would be updated and then disappear
after the statement is finished, thus doing nothing.

2) You go to the WHERE clause. All rows (if any!) that test TRUE are
marked as a subset. If there is no WHERE clause, then the entire table
is marked. The name of this set/pseudo-table is OLD in Standard
SQL.

3) You go to the SET clause and construct a set/pseudo-table called
NEW. The rows in this table are build by copying values from the
columns are not mentioned from the original row to the NEW row. The
columns are assigned all at once. That is, the unit of work is a row,
not one column at a time.

4) The OLD subset is deleted and the NEW set is inserted. Those are
the proprietary terms used in SQL Server, too. This is why

UPDATE Foobar
SET a = b, b = a;

Swaps the values in the columns a and b. The engine checks constraints
and does a ROLLBACK if there are violations.

In full SQL-92, you can use row constructors to say things like:

UPDATE Foobar
SET (a, b)
= (SELECT x, y
FROM Floob AS F1
WHERE F1.keycol= Foobar.keycol);

The proprietary, non-standard UPDATE.. FROM.. syntax is a total
disaster in the ANSI model and in implementation, but that is another
rant.

Trying to UPDATE the temporary result of a JOIN syntax would be
useless - that temporary result disappears at the end of the statement
and never touches the base tables.

[Back to original message]