How does UPDATE statement work?

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

Re: How does UPDATE statement work?

On 11 May, 06:41, kdpo wrote:
> 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/ms130214(en-US,SQL.90).as px
--

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.

Re: How does UPDATE statement work?

Thank you


-A

Re: How does UPDATE statement work?

"--CELKO--" 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