Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01427: single-row subquery returns more than one row
ORA-01427: single-row subquery returns more than one row [message #303748] Sun, 02 March 2008 11:52 Go to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
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
,'SSALES'
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
));


Regards,
Gyanesh
Re: ORA-01427: single-row subquery returns more than one row [message #303752 is a reply to message #303748] Sun, 02 March 2008 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one of my answer to one of your previous posts:
Michel Cadot wrote on Sun, 06 January 2008 19:02
...
Also
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Post execution formatted and with line numbers.

Regards
Michel

Re: ORA-01427: single-row subquery returns more than one row [message #304002 is a reply to message #303752] Mon, 03 March 2008 11:47 Go to previous messageGo to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
UPDATE sq_Product_Price sqpp
SET    (Net_Price,
        List_Price,
        Update_Date) = (SELECT Ifp.NetPrice,
                               Ifp.Pricepmt,
                               SYSDATE
                        FROM   If_ezs_Price Ifp,
                               sq_Currency sCur,
                               sq_Price_Term sprTerm,
                               sq_Country_Price_deScriptOr cpd
                        WHERE  Ifp.Product_Code = sqpp.Product_Code
                               AND Ifp.Currency = sCur.hp_Code
                               AND Ifp.Price_Term = sprTerm.Old_Code
                               AND Ifp.Country = cpd.pd_Geography_Code
                               AND sCur.Iso_Code = cpd.pd_Currency_Code
                               AND sprTerm.Standard_Code = cpd.pd_Term_Code)
WHERE  (sqpp.Product_Code,
        sqpp.pd_Geography_Code,
        sqpp.pd_Currency_Code,
        sqpp.pd_Term_Code) IN (SELECT DISTINCT sqp2.Product_Code,
                                               sqp2.pd_Geography_Code,
                                               sqp2.pd_Currency_Code,
                                               sqp2.pd_Term_Code
                               FROM   sq_Product_Price sqp2,
                                      If_ezs_Price Ifp,
                                      sq_Currency sCur,
                                      sq_Price_Term sprTerm,
                                      sq_Country_Price_deScriptOr cpd
                               WHERE  sqp2.Product_Code = Ifp.Product_Code
                                      AND Ifp.Currency = sCur.hp_Code
                                      AND Ifp.Price_Term = sprTerm.Old_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.ss_DateUpd <> sqp2.Update_Date
                                            OR Ifp.NetPrice <> sqp2.Net_Price
                                            OR Ifp.Pricepmt <> sqp2.List_Price));


Micheal,hope this looks good enough?



code tags added by moderator; please re-read the forum guide and use the code tags yourself next time; also still missing line numbers

[Updated on: Mon, 03 March 2008 11:59] by Moderator

Report message to a moderator

Re: ORA-01427: single-row subquery returns more than one row [message #304006 is a reply to message #304002] Mon, 03 March 2008 12:06 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
It is the sub-query below that is returning more than one row when it must return only one row, otherwise Oracle can't tell which row to pick the values from for the update. Try running the sub-query by itself, substituting an actual value for sqpp.product_code to see how many rows are returned, then find a way to narrow it down. If the netprice and pricepmt are the same for all of those rows, then you can just add "and rownum=1" to the sub-query to restrict it to one row, but there should be some sort of unique identifier that should allow you to do that more precisely and efficiently.

SELECT Ifp.NetPrice,
                               Ifp.Pricepmt,
                               SYSDATE
                        FROM   If_ezs_Price Ifp,
                               sq_Currency sCur,
                               sq_Price_Term sprTerm,
                               sq_Country_Price_deScriptOr cpd
                        WHERE  Ifp.Product_Code = sqpp.Product_Code
                               AND Ifp.Currency = sCur.hp_Code
                               AND Ifp.Price_Term = sprTerm.Old_Code
                               AND Ifp.Country = cpd.pd_Geography_Code
                               AND sCur.Iso_Code = cpd.pd_Currency_Code
                               AND sprTerm.Standard_Code = cpd.pd_Term_Code

Previous Topic: ORA-00036: maximum number of r
Next Topic: Oracle 10g DML error logging
Goto Forum:
  


Current Time: Sat Dec 03 22:00:26 CST 2016

Total time taken to generate the page: 0.12260 seconds