Update a table's multile columns in a sub query

Question

Update a table's multile columns in a sub query

csraocvs

SSC Journeyman

Points: 94
More actions
November 15, 2002 at 11:08 am

#81246

I appreciate if anybody can help me to write this UPDATE command. The requirement is to update 3 columns in a table with a subquery accessing same table but different row.
update MY_TAB1
set ( acronym, version)= ( select acronym , version
from MY_TAB1
where Pri_Key=121 )
where Pri_Key=117
This does not work and I do not know how can I update multiple columns at the same time. Thanks.
--Chandra Cheedella

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mdburr Default port Points: 1483 More actions · Answer 1



UPDATE MY_TAB1

   SET acronym = (SELECT acronym

                    FROM MY_TAB1

                   WHERE Pri_Key = 121),

       version = (SELECT version

                    FROM MY_TAB1

                   WHERE Pri_Key = 121)

 WHERE Pri_key = 117

csraocvs SSC Journeyman Points: 94 More actions · Answer 2

Thanks for your help. This is a round about procedure but I think this is the only to do in SQL Server. In Oracle, it is quite simple.

--Chandra

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

seems pretty easy in SQL Server as well.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

Looks pretty straight forward to me too, though I think I would use variables to hold the values and do a single select to get them. Another statement to update them.