SQL Update: ERROR: Subquery evaluated to more than one row.

[Bankshot]

Hi,

I encountered the same issue when I tried to update using the syntax below, and got an error message:

ERROR: Subquery evaluated to more than one row.

Could someone look at my code and see if this is a syntax error and help me correct it?

proc sql;
update lgddata.lgd_data_auto new
set auto_dfrt_other =
(select dfrt_other
from lgd2014.lgd_data_auto
where new.proc_yymm_dt = proc_yymm_dt);
quit;

Thanks

[ChrisHemedinger · Accepted solution]

It's not a syntax error, but a runtime problem because your subquery (apparently) yields more than one record, and the UPDATE assignment needs exactly one value to put in.

See what just this clause yields:

select dfrt_other 
from lgd2014.lgd_data_auto 
where new.proc_yymm_dt = proc_yymm_dt

[Bankshot]

Thanks Chris. When you say "subquery (apparently) yields more than one record", are you referring to the number of unique value (in this case dfrt_other)? I just did a check on the number of unique values by each date, it seems all dates have unique rate values:

proc sql;
select
proc_yymm_dt, count(distinct dfrt_other)
from lgd2014.lgd_data_auto
group by proc_yymm_dt
having count(distinct dfrt_other) > 1;
quit;

The above code yields to a blank result

[PGStats]

As said in the error message, it is the number of rows that must be less than or equal to one. Rows with duplicate dfrt_other values are a problem too. Replace count(distinct dfrt_other) with count(*) to check the number of rows. 

[PGStats]

I suspect you are missing a condition in your where clause. Get the problematic dates with the query:

proc sql;
select a.proc_yymm_dt, count(*) as n
from 
	lgddata.lgd_data_auto as a inner join
	lgd2014.lgd_data_auto as b on a.proc_yymm_dt = b.proc_yymm_dt
group by a.proc_yymm_dt
having count(*) > 1;
quit;

(untested)