|
Posted by Greg D. Moore \(Strider\) on 05/11/07 22:48
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1178906609.555509.30850@h2g2000hsg.googlegroups.com...
> The ANSI model of an UPDATE is that it acts as if
>
Note that Joe is talking about the logical level.
At the physical level given the update Joe describes below, if you have say
just two rows in the table (and more accurately it's a heap), it's quite
possible that the row identifier will be updated, in place, but the columns
remain the same.
Note however, the OLD/NEW pseudo-table paradigm still exists, regardless of
how the engine itself performs the update.
> 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.
>
>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Navigation:
[Reply to this message]
|