SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS Callable Sequel: update table

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

SAS Callable Sequel: update table

Hello Everyone,

I want to update a table based on values in another table, but I keep on getting errors in my log on the syntax when I use the "FROM statement". I am basing my code off an example I found and do not see any syntax errors. There is a comma (",") after N.ToyName in the example but that is because they are adding an additional variable. If you guys have any suggestions that would be great! Thank you!

PROC SQL;

UPDATE TEMP2 

   SET RUCC_2013 = m.RUCC_2013

FROM TEMP2 t JOIN minnesota m

     ON t.SEQNO = m.sequence_number;


Here is the log:


175  PROC SQL;

176  UPDATE TEMP2

177     SET RUCC_2013 = minnesota.RUCC_2013

178  FROM TEMP2 t JOIN minnesota m

     ----

     22

     76

ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE,

ERROR 76-322: Syntax error, statement will be ignored.

179       ON t.SEQNO = m.sequence_number;

Here is the example I based my code off of:

Updating a Table Based on Values in a Different Table

There may be times when you don’t want to manually write a bunch of UPDATE statements with different literal strings to update your table.  Suppose I wanted to change all the prices of my Toys with a single UPDATE statement. I can do that with the following code:

UPDATE Toy   

SET ToyName = N.ToyName,      

Price = N.Price

FROM Toy T JOIN NewToyPrice N     

ON T.ID = N.ID; 

In this code I updated the Toy table based on values in another table, in this case a table named NewToyPrice.  To accomplish that I joined the TOY table to my NewToyPrice table based on the ID column.  I then used the NewToyPrice column values for ToyName and Price to update my Toy table column values on rows that have matching column ID values.


Accepted Solutions
Solution
‎04-27-2015 01:43 PM
Respected Advisor
Posts: 4,920

Re: SAS Callable Sequel: update table

Posted in reply to daszlosek

The is no FROM clause in the SQL UPDATE syntax (the table involved is already mentioned after UPDATE). Try instead:

PROC SQL;

UPDATE TEMP2  as t

   SET RUCC_2013 = (select RUCC_2013 from minnesota where sequence_number = t.SEQNO);

quit;

PG

PG

View solution in original post


All Replies
Solution
‎04-27-2015 01:43 PM
Respected Advisor
Posts: 4,920

Re: SAS Callable Sequel: update table

Posted in reply to daszlosek

The is no FROM clause in the SQL UPDATE syntax (the table involved is already mentioned after UPDATE). Try instead:

PROC SQL;

UPDATE TEMP2  as t

   SET RUCC_2013 = (select RUCC_2013 from minnesota where sequence_number = t.SEQNO);

quit;

PG

PG
Frequent Contributor
Posts: 76

Re: SAS Callable Sequel: update table

Thank you a ton PG!

I wonder why the example code I found used a FROM clause in SQL Update if it is incorrect syntax?

Much appreciated,

Daszlosek

🔒 This topic is solved and locked.

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

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