SEO

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

> 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" <leif@neland.dk> wrote in message
news:4822d7e6$0$56781$edfadb0f@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.)
>
>

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

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> skrev i en meddelelse
news:VyCUj.15179$V14.7043@nlpi070.nbdc.sbc.com...
> > 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
>
Thanks, worked perfectly.

Now I can update my system can be updated, so customers can order stuff for
both their loved ones, and their wives, without the ordrers getting mixed up
:-) (I had delivery adress in the customer table, not the order table)

Leif

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.