copy data to another table via sql update

philip_ — Wed, 12 Jul 2017 12:20:07 GMT

Hello,

I have two tables:

Table1
Date Classifier Count
2017 A
2016 A
2017 B
2016 B

(Note, that the Count column is empty in Table1.)

Table2
Date Classifier Count
2017 A 100

I wanted to copy the Count value from Table2 into Table1, where Date and Classifier are matching, so that I get as an updated table:

Updated Table1
Date Classifier Count
2017 A 100
2016 A
2017 B
2016 B

Therefore, I tried the following query:

PROC SQL;
Update Table1
Set t1.Count = t2.Count
FROM Table1 t1, Table2 t2
WHERE t1.Date = t2.Date
ANDt1.Classifier = t2.Classifier:
QUIT;

However, i only got a syntax error. How can I solve this? Thanks a lot!

Re: copy data to another table via sql update

RW9 — Wed, 12 Jul 2017 12:31:13 GMT

proc sql;
  update TABLE1 A
  set COUNT=(select COUNT from TABLE2 where DATE=A.DATE and CLASSIFIER=A.CLASSIFIER);
quit;

Try that. If you want tested code then post test data in the form of a datastep.