BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daszlosek
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
PGStats
Opal | Level 21

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
daszlosek
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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