Prev: Cannot see SQL Server 2008 on other node!
Next: Remove characters in an ASCII decimal range: How To?
From: --CELKO-- on 17 May 2010 23:10
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.
From: tshad on 18 May 2010 21:20
Got you.

It is like moving all values to a temporary location and them moving them
into the specified location. Obviously, it would be done a little more
efficiently.

I was pretty sure that was the case but wanted to make sure.

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:n4r3v51nrrnrfhh0rqpao9s6i41d8s4d1l(a)4ax.com...
> Think of having a snapshot of the column values before the update,
> then use the snapshot values to update the columns. All column values
> are update in a single step (using the snapshot values), there is no
> order of applying the update. This is a fundamental concept in SQL,
> which makes it differ from the procedural languages (where iteration
> and order rule).
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: J M De Moor on 19 May 2010 01:08
>
> The proprietary, non-standard UPDATE.. FROM.. syntax is a total
> disaster in the ANSI model and in implementation
>
>

At it again, huh? (You know I will never let this go.) First of all
the above sentence makes no sense. Is there really a "proprietary,
non-standard UPDATE..FROM syntax"..."in the ANSI model"?

Either way, the fact is that UPDATE..FROM is NOT a disaster and,
besides, this assertion is completely off topic, given that the OP was
asking about swapping column values.

Joe De Moor
From: Marilyn on 19 May 2010 09:47
Hi,

Just happen to "glance-in" in your discussions. This is very informative
for me. Thank you.

Marilyn

"--CELKO--" wrote:

> 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.
> .
>
From: --CELKO-- on 20 May 2010 07:22
/*
DROP TABLE OrderDetails, Orders;
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);

INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);

CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));

INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);

SELECT * FROM Orders;

UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;

results -- see item #1; last physical value
1 205.00 - where is the $500.00?
2 490.95
3 480.00
*/

--repeat with new physical ordering
DELETE FROM OrderDetails;
DELETE FROM Orders;
DROP INDEX OrderDetails.foobar;

-- index will change the execution plan
CREATE INDEX foobar ON OrderDetails (order_nbr, item_price);

INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);

INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);

UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;

SELECT * FROM Orders;

/*
Results
1 500.00
2 490.95
3 480.00
*/

What is the first property that you must have in an INDEX? It cannot
change the results of a statement, only the performance. See the
problem? That is the ANSI model, along with set-oriented updating as I
previously outlined.

This would not have happened with the ANSI syntax. That's the point
that I am trying to make. The ANSI equivalent of the incorrect query
above is

UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);

Or I could use a MERGE. That is the direction that several MVPs and
language designers want Microsoft to take when the uPDATE.. FROM is
deprecated.

This will of course result in an error, and even the most junior of
junior programmers will eventually figure out (probably by asking a
senior) what's wrong. At that point, either the query is corrected to
match the request, or a note is sent back to management asking for a
clarification of the ambiguity in the request.

Bottom line: UPDATE FROM *can* be safely used - but only if you are
FOREVER certain that no single row in the target table can EVER be
joined to more than one row in the source table(s); FOREVER in the
ENTIRE LIFETIME of the schema; FOREVER across all programmers yet to
come.

I like to err on the safe side, I do not bet only an endless stream of
100% perfect programmers.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Cannot see SQL Server 2008 on other node!
Next: Remove characters in an ASCII decimal range: How To?