PROC SQL - UPDATE

[SASAna]

Hi All,

I am trying to update the Oracle table with SAS dataset contents with some merge conditions & queried as belwo and getting error message as  ERROR 73-322: Expecting an =.   

SAS dataset - MANUAL_TEMP

Oracle table -dbms.oracle_table

libname dbms oracle user=CCCC password= AAAA path= BBBB schema=xyz;

PROC SQL;
update dbms.oracle_table(dbkey=XYZ) A
SET
A.test_date = (SELECT B.test_date FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6),
A.test_upload = (SELECT B.test_upload FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6),
A.test_reason = (SELECT B.test_reason FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6),
A.test_exclusion = (SELECT A.test_exclusion || '|' || B.test_exclusion FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6)
where exists (
select 1
from MANUAL_TEMP B
where
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6);

QUIT;

[ballardw]

Post the code from log along with the error message. It will help if you paste it into the box opened by clicking on the "run" icon at the top of you message to preserve formatting as the _ character shows exactly where the unexpected value appears.

Note that may not be the cause of the error as missing or extra semicolons or commas before that line may be the actual cause.

[SASAna]

Sorry, it has some PHI information to provide  actual code from the log, so i just changed them to example.

update dbms.&MyTable(dbkey=XYZ) A

993 SET
994 A.temp1= (SELECT B.temp1  FROM MANUAL_TEMP B
_
73
76
ERROR 73-322: Expecting an =.

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

Thanks for looking into,

[Ksharp]

You can use the following options to see what kind of sql SAS passed to Oracle and debug it from there.

options sastrace=',,,d' sastraceloc=saslog;

[SASAna]

I tried adding that, but It didnt help me much to trace where it is going wrong.

Thanks,

[Ksharp]

Can you remove  "(dbkey=id)" ?

[SASAna]

Thanks for the suggestion , But Still the same error.

[Ksharp · Accepted solution]

I know what is going on , Remove 'A.' from the variable

993 SET
994 A.temp1= (SELECT B.temp1  FROM MANUAL_TEMP B

---->

993 SET
994 temp1= (SELECT temp1  FROM MANUAL_TEMP B

[SASAna]

That really worked !!! Thanks much for helping me out, learned something new today.

Thanks Again

[suraj]

I am using the following query to update a table . Can you please help why we are using exists here? Thanks!

where exists (
select 1
from MANUAL_TEMP B
where
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6);

[Ksharp]

exist(...) will return TRUE if you can SELECT ANY obs from that condition.

WHERE  means update the obs which satisfy that condition ( exist will return TRUE).

[suraj]

Thanks a lot for help.. it worked for me.

I am just curious to know why we are using where again when we are already using it in subquery.

I also used it in same way and it's working for me. but just wants to clear my doubt. Thanks in advance!

Regards,

Suraj

[Ksharp]

I also confused before.

WHERE clause here is to update sub-dataset,

if you don't specify WHERE then SQL will update all the obs.