DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 6

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

[ Edited ]

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

Community Manager
Posts: 2,761

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

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
Occasional Contributor
Posts: 6

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

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

Respected Advisor
Posts: 4,646

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

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. 

PG
Respected Advisor
Posts: 4,646

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

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)

PG
Ask a Question
Discussion stats
  • 4 replies
  • 130 views
  • 1 like
  • 3 in conversation