MS SQL

update two rows in one query

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: May 2007

Posts: 1

Reputation: rchokler is an unknown quantity at this point

Solved Threads: 0

rchokler

Offline

Newbie Poster

update two rows in one query

May 13th, 2007

UPDATE table1
SET ID,[DESC] =
(SELECT ID,[DESC]
FROM tmptable
WHERE tmptable.ID_SUN = table1.ID_SUN)
WHEREEXISTS
( SELECT ID,[DESC]
FROM tmptable
WHERE tmptable.ID_SUN = [Table1].ID_SUN)
I tired this but i coudn't uptade tow columns at once
do i need to seperate them or i have any mistake?

•

Join Date: Mar 2007

Posts: 28

Reputation: cfAllie is an unknown quantity at this point

Solved Threads: 0

cfAllie

Offline

Light Poster

Re: update two rows in one query

May 13th, 2007

Yes, you need to separate them. The syntax should be more like

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

UPDATE SomeTable
SET   Column1 = SomeValue1,
        Column2 = SomeValue2,
        Column3  = ....

It doesn't look like you need a subquery either. A simple join should be sufficient. Something like

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
UPDATE t1
SET    t1.ID = tmp.ID,
         t1.[DESC] = tmp.[DESC]
FROM   
        tmptable tmp INNER JOIN table1 t1
        ON tmp.ID_SUN = t1.ID_SUN
UPDATE t1
SET    t1.ID = tmp.ID,
         t1.[DESC] = tmp.[DESC]
FROM   
        tmptable tmp INNER JOIN table1 t1
        ON tmp.ID_SUN = t1.ID_SUN