SQL SET..SELECT

[gamotte]

Hi all,

I would like to update several columns in a table from their

counterparts in another table.

If I only wanted to update one variable I could do :

UPDATE T1

SET X = (

     SELECT X

     FROM T2

     WHERE T2.ID=T1.ID

);

What if I also want to update variable Y ? Is there a way to achieve the result

with only one select ?

I tried the following with no luck :

UPDATE T1

SET (X,Y) = (

    SELECT X, Y

    FROM T2

    WHERE  T2.ID=T1.ID

);

Thanks

[LinusH · Accepted solution]

I am usually a fan of SQL, but when it comes to update syntax, no...

To my knowledge, you need multiple SELECTs to do this.

So, I would suggest to use a data step update, using MODIFY. Should cover most scenarios of updating using a transaction table.

[gamotte]

OK, I reached the same conclusion by searching similar threads on other forums.

Thanks