ERROR: Subquery evaluated to more than one row.

[radha009]

Can you please help: i want to create a new column with type for matching Id. I am getting the error as  ERROR: Subquery evaluated to more than one row

Proc sql;
alter table dash.fruits
add type char(30) format = $30.;
update dash.fruits as A
set Type = (select b.type from dash.category as b
                  where A.id-b.id);
quit;

[Shmuel]

Did you mean: where A.id = b.id); - you have a hyphen instead equal sign.

[radha009]

oops typo. i still got the same error

[RW9]

The error tells you everything you need to know, the subquery:

 (select b.type from dash.category as b
                  where A.id-b.id)

Returned more than one row of data.  Check in dash.category, maybe you have duplicate rows, or more than one merge on id.  This is where showing some test data in the form of a datastep really helps!

[emrancaan · Accepted solution]

Proc sql;
alter table dash.fruits
add type char(30) format = $30.;
update dash.fruits as A
set Type = (select DISTINCT b.type from dash.category as b
                  where A.id=b.id);
quit;