07-24-2017 02:16 PM - last edited on 07-24-2017 02:43 PM by ChrisHemedinger
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?
update lgddata.lgd_data_auto new
set auto_dfrt_other =
where new.proc_yymm_dt = proc_yymm_dt);
07-24-2017 02:45 PM
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
07-24-2017 04:16 PM
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_yymm_dt, count(distinct dfrt_other)
group by proc_yymm_dt
having count(distinct dfrt_other) > 1;
The above code yields to a blank result
07-24-2017 06:37 PM
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.
07-24-2017 03:43 PM
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;