Re: How does UPDATE statement work?

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.




.

Relevant Pages

  • Re: Update order
    ... It cannot have an alias because an alias ... You go to the WHERE clause. ... disaster in the ANSI model and in implementation, ... Trying to UPDATE the temporary result of a JOIN syntax would be ...
    (microsoft.public.sqlserver.programming)
  • Re: Update order
    ... It cannot have an alias because an alias ... You go to the WHERE clause. ... UPDATE Foobar ... Trying to UPDATE the temporary result of a JOIN syntax would be ...
    (microsoft.public.sqlserver.programming)
  • Re: Aliases and the WHERE clause
    ... The one hope we could hold is that future releases of engines will build on ... To me, SQL also feels just like a set of principles, or if you wish, mental ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ...
    (microsoft.public.access.queries)
  • Trying to find a maximum value amongst 3 columns in a dB row
    ... The column prefix 'a1' does not match with a table name or alias name ... used in the query. ... Either the table is not specified in the FROM clause ...
    (comp.databases.sybase)
  • Re: Parameter Query on an expression
    ... It is because you CANNOT USE ALIAS in the WHERE clause, but have to repeat the expression the alias stands for. ... but two levels of alias: LotsAcc depends on CountOfqtrce which is itself an alias for a computed expression. ... Furthermore, since the computed expression involves aggregate, some of the criteria have to be moved in the HAVING clause. ... I would expect it to determine what the "Profit"> value ...
    (microsoft.public.access.queries)