Help using Base SAS procedures

SQL SET..SELECT

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

SQL SET..SELECT

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


Accepted Solutions
Solution
‎11-22-2012 08:05 AM
Super User
Posts: 5,254

Re: SQL SET..SELECT

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.

Data never sleeps

View solution in original post


All Replies
Solution
‎11-22-2012 08:05 AM
Super User
Posts: 5,254

Re: SQL SET..SELECT

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.

Data never sleeps
Regular Contributor
Posts: 194

Re: SQL SET..SELECT

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

Thanks

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 290 views
  • 0 likes
  • 2 in conversation