Can you tell me how to update multiple rows in oracle as when I fired the update statement it gave me the following error
UPDATE BI_BSELATEST_LATESTPRICESTEST
SET PREVIOUS_DAY_CLOSE =
(SELECT DISTINCT aa.DLYPRICE_CLOSE
FROM DATAFEED_EQTY.FEED_DLYPRICE aa ,
(
SELECT a.sc_code , MAX(a.DLYPRICE_DATE) as max_date
from DATAFEED_EQTY.FEED_DLYPRICE a,BI_BSELATEST_LATESTPRICES b
where a.SC_CODE = b.SC_CODE
and a.ST_EXCHNG = 'BSE'
and a.DLYPRICE_DATE < b.upd_time
group by a.sc_code
) bb
WHERE aa.SC_CODE = bb.sc_code
and aa.DLYPRICE_DATE = max_date)
ORA-01427: single-row subquery returns more than one row
Thanks in advance
A subquery in your statement is wrong. You left off either a WHERE
or FIRST
clause, and now it's returning multiple values when it shouldn't.
You're basically trying to say PREVIOUS_DAY_CLOSE
should be multiple values at the same time. I'm guessing you left off a WHERE
clause on your subselect, which would link the results of that subquery to the particular row you're trying to update. Something like (note the bolded line):
UPDATE BI_BSELATEST_LATESTPRICESTEST
SET PREVIOUS_DAY_CLOSE =
(SELECT DISTINCT aa.DLYPRICE_CLOSE
FROM DATAFEED_EQTY.FEED_DLYPRICE aa ,
(
SELECT a.sc_code , MAX(a.DLYPRICE_DATE) as max_date
from DATAFEED_EQTY.FEED_DLYPRICE a,BI_BSELATEST_LATESTPRICES b
where a.SC_CODE = b.SC_CODE
and a.ST_EXCHNG = 'BSE'
and a.DLYPRICE_DATE < b.upd_time
group by a.sc_code
) bb
WHERE aa.SC_CODE = bb.sc_code
and aa.DLYPRICE_DATE = max_date
AND bb.sc_code = BI_BSELATEST_LATESTPRICESTEST.sc_code
)
Although, I'll be honest, I'm not exactly sure what you're trying to do with this query.