How does UPDATE statement work?

[kdpo]

Could someone tell where I can find out if it's true that during
UPDFATE SQL Serve deletes data from table, and then inserts new one.

Thanks

-A

[David Portas]

On 11 May, 06:41, kdpo <[e-mail]:

Could someone tell where I can find out if it's true that during
UPDFATE SQL Serve deletes data from table, and then inserts new one.

Thanks

-A

Replied in microsoft.public.sqlserver.server
Please do not multi-post.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[--CELKO--]

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.

[kdpo]

Thank you
-A

[Greg D. Moore \(Strider\)]

"--CELKO--" <jc*******@earthlink.netwrote in message
news:11*********************@h2g2000hsg.googlegrou ps.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