Posted by Neil on 06/02/05 13:26

> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
>
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */

Yes, that was what I was looking for (though I needed to use UPDATE T1
SET.... From T1, (Select foo....) As T2...)

Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:

UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@VALUE) AS T2
WHERE T1.ID=@VALUE

Do you see any problem with that?


>> I've always used cursors for
>> scrolling through resultsets
>
> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.

I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.

Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.

Thanks for your help!

Neil





>
> --
> David Portas
> SQL Server MVP
> --
>

[Back to original message]