updating multiple columns with subselect

[Leif Neland]

This is not supported in MSsql.

update T1 set (theUpdatedValue, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)

Is there a workaround?

Other than doing it in a loop from eg asp, over either fields (one statement
per field), or over records (a query with a loop which for each row does a
select from one table, update other table with the selected values.)

[Dan Guzman]

This is not supported in MSsql.

>
update T1 set (theUpdatedValue, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)

Is there a workaround?

You can use the proprietary UPDATE...FROM syntax:

UPDATE T1
SET
theUpdatedValue = T2.theTop,
theOtherValue = T2.theValue
FROM T2
WHERE
T2.theKey = T1.theID

Or with an alias:

UPDATE a
SET
theUpdatedValue = b.theTop,
theOtherValue = b.theValue
FROM T1 a
JOIN T2 b ON
b.theKey = a.theID

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Leif Neland" <le**@neland.dkwrote in message
news:48***********************@dtext02.news.tele.d k...

This is not supported in MSsql.

update T1 set (theUpdatedValue, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)

Is there a workaround?

Other than doing it in a loop from eg asp, over either fields (one
statement
per field), or over records (a query with a loop which for each row does a
select from one table, update other table with the selected values.)

[Plamen Ratchev]

On SQL Server 2005 you can use CTE to perform the update:

WITH UpdateSet
AS
(SELECT theUpdatedValue, theOtherValue, theTop, theValue
FROM T1
JOIN T2
ON T1.theID = T2.theKey)
UPDATE UpdateSet
SET theUpdatedValue = theTop,
theOtherValue = theValue;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

The proprietary syntax does not work all the time:

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?

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);

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 application; 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. You correctly guessed the row constructor
syntax that is standard in ANSI/ISO SQL, by the way.