Re: join in an update statement

part 1 , the answer is no.
part 2, if you want to copy multiple columns, try grouping those as in

        update table1 set (qty1, qty2) = (select table2.qty1, table2.qty2 from table2, table1

        where table2.key=table1.key);

"Jeff Goodman" <JGoodman10_at_nyc.rr.com> wrote in message news:6LWe8.35514$Ci6.6226336_at_typhoon.nyc.rr.com...
> Can you do a join in an update statement. I only want to update one
table,
> but I want to set values from table 1 to equal values from table 2. For
> example:
>
> update table1, table2
> set table1.quantity = table2.quantity
> where table1.key = table2.key
>
> The only way I can think to do it is to say:
>
> update table1
> set table1.quantity = (select table2.quantity where table1.key =
table2.key)
>
> But if I have more columns I want to update, I would have to have a
seperate
> subselect for each column, which could become pretty costly. I would also
> like to test it with a select clause to see what would be updated but with
> the second update, I cant think of a corresponding select.
>
> Thanks.
> Jeff
>
>
>
Received on Tue Feb 26 2002 - 23:10:33 CST