Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-01427: single-row subquery returns more than one row

SOLVED
Go to solution
Gyankr
Frequent Advisor

ORA-01427: single-row subquery returns more than one row

Hi,

I am trying to update the price changes on table if there is a change in another table.I searched the forum but could not get any satisfying result.The query returns multiple rows and even though i used where..in or where exists clause i am not able to update multiple rows.This is using multiple tables to satisfy various criteria.Any suggestions?

update SQ_PRODUCT_PRICE set (
net_price
,list_price
,update_date
,update_source
)=
(
select distinct ifp.netprice
,ifp.pricepmt
,sysdate
,'UPD'
from if_ezs_price ifp
, sq_currency scur
, sq_price_term sprterm
, sq_country_price_descriptor cpd
,sq_product_price sqpp
where sqpp.product_code=ifp.product_code
and ifp.country = cpd.pd_geography_code
and scur.iso_code = cpd.pd_currency_code
and sprterm.standard_code = cpd.pd_term_code
and ifp.currency = scur.hp_code)
where (sqpp.PRODUCT_CODE,sqpp.COUNTRY_CODE,sqpp.PD_GEOGRAPHY_CODE,sqpp.PD_CURRENCY_CODE,sqpp.PD_TERM_CODE) IN
(select distinct sqp2.PRODUCT_CODE,sqp2.COUNTRY_CODE,sqp2.PD_GEOGRAPHY_CODE,sqp2.PD_CURRENCY_CODE,sqp2.PD_TERM_CODE
from sq_product_price sqp2
, sq_currency scur
, sq_price_term sprterm
, sq_country_price_descriptor cpd
,if_ezs_price ifp
where sqp2.product_code=ifp.product_code
and ifp.country = cpd.pd_geography_code
and scur.iso_code = cpd.pd_currency_code
and sprterm.standard_code = cpd.pd_term_code
and ifp.currency = scur.hp_code
and (ifp.netprice <> sqp2.net_price
or ifp.pricepmt <> sqp2.list_price
));
4 REPLIES
Kapil Jha
Honored Contributor

Re: ORA-01427: single-row subquery returns more than one row

it just a result of where clause , your where clause must be returning more than one row.Have you used some primary key??
Its always better to use primary key in where clause because it ensure that only required rows would be output.

For particular case query is too short for a unix guy to understand.I would suggest to rewrite the query you may find the problem.
Hope this help.
Kapil
I am in this small bowl, I wane see the real world......
Yogeeraj_1
Honored Contributor

Re: ORA-01427: single-row subquery returns more than one row

hi,

You must ensure that the subquery below returns a SINGLE ROW!:
(SELECT DISTINCT ifp.netprice,
ifp.pricepmt,
sysdate,
'UPD'
FROM if_ezs_price ifp,
sq_currency scur,
sq_price_term sprterm,
sq_country_price_descriptor cpd,
sq_product_price sqpp
WHERE sqpp.product_code = ifp.product_code
AND ifp.country = cpd.pd_geography_code
AND scur.iso_code = cpd.pd_currency_code
AND sprterm.standard_code = cpd.pd_term_code
AND ifp.currency = scur.hp_code)

This is the only part that is failing depending, of course, on the constraints on the underlying tables (UNIQUE, PRIMARY KEY).

Note that DISTINCT does not mean a SINGLE ROW will be returned.

Have a look at the constraints and data.

hope this helps!
kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: ORA-01427: single-row subquery returns more than one row

hi again,

You can verify if the query is returning a SINGLE ROW by running the following query:

SELECT DISTINCT ifp.netprice,
ifp.pricepmt,
sysdate,
'UPD', count(*)
FROM if_ezs_price ifp,
sq_currency scur,
sq_price_term sprterm,
sq_country_price_descriptor cpd,
sq_product_price sqpp
WHERE sqpp.product_code = ifp.product_code
AND ifp.country = cpd.pd_geography_code
AND scur.iso_code = cpd.pd_currency_code
AND sprterm.standard_code = cpd.pd_term_code
AND ifp.currency = scur.hp_code
group by 1,2,3,4
having count(*) > 1;

Let us know if you need any further assistance.

kind regards,
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Solution

Re: ORA-01427: single-row subquery returns more than one row


update table_1 set (
column_list_1
) =
(select distinct
column_list_2
from table_list_1
where criteria_list_1
)
where (column_list_3) IN
(select distinct column_list_4
from table_list_2
where criteria_list_2
)
;

The problem is on criteria_list_1.
The distinct cannot guarantee the single result.

Good luck.
Xiaogang