SAS Callable Sequel: update table

[daszlosek]

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.

[PGStats · Accepted solution]

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

[daszlosek]

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