ERROR: Subquery evaluated to more than one row

[sasbanker]

I have 2 tables, WORK.HRIS_OR and WORK.FAKC_OR.

Both tables have the key field Standard_ID.

Both tables are sort by Standard_ID, and have no duplicate Standard_ID's.

I want to update the WORK.HRIS_OR table values with the values from the corresponding fields in WORK.FAKC_OR.

I'm getting the error "ERROR: Subquery evaluated to more than one row"

I'm a newbie with no training and little experience, so please be specific in your response.

Thanks,

up

WORK.HRIS_OR created, with 274,147 rows

WORK.FAKC_OR created, with 409 rows

21276  /*  Updates HRIS_OR Table with FAKC_OR Table Values */

21277  proc sql;

21278      update HRIS_OR

21279          set

21280          AltLob = (select AltLob from FAKC_OR),

21281          Organization = (select Organization from FAKC_OR),

21282          Line_of_Business_Name = (select Line_of_Business_Name from FAKC_OR),

21283          Sub_Line_of_Business_Name = (select Sub_Line_of_Business_Name from FAKC_OR),

21284          Division_Name = (select Division_Name from FAKC_OR),

21285          Sub_Division_Name = (select Sub_Division_Name from FAKC_OR)

21286      where Standard_ID in (select Standard_ID from FAKC_OR);

ERROR: Subquery evaluated to more than one row.

21287  quit;

[Kurt_Bremser]

A subquery like

select Sub_Division_Name from FAKC_OR

will give you all Sub_Division_Name from the table in a list, but you need only a single value here for the set, so you would need to specify which observation from FAKC_OR you want selected. Unpractical to do it this way.

For what you want to do, have a look at

Step-by-Step Programming with Base SAS(R) Software

(UPDATE in a data step)

and

SAS(R) 9.2 SQL Procedure User's Guide

(UPDATE in SQL)

[sasbanker]

Thanks.  Muchly appreciated.

[RW9 · Accepted solution]

The key to your question lies in this statement:

"I want to update the WORK.HRIS_OR table values with the values from the corresponding fields in WORK.FAKC_OR."

You haven't specified the rows to be updated, this for example:

AltLob = (select AltLob from FAKC_OR),

Selects every record from FAKC_OR and tries to assign it to one record in HRIS_OR which is why you get the fail.

Not knowing your data I could suggest:

proc sql;

  update HRIS_OR A

  set  AltLob=(select AltLob from FAKC_OR where ID=A.ID and SOME_OTHER_ID=A.SOME_OTHER_ID),

...

I would suggest however that you do a more conditional procedure:

proc sql;

     create table WANT as

     select     COALESCE(A.ID,B.ID) as ID,

                   case     when A.VALUE is null then B.VALUE

                               else A.VALUE end as VALUE,

                    ...

     from       HRIS_OR A

     full join   FAKC_OR B

     on          A.ID=B.ID;

quit;

This way you have more control over what gets populated, for instance if you have missing data in the new data but not in the old etc.