Update multiple rows in oracle
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.
I think what you want is this:
UPDATE BI_BSELATEST_LATESTPRICESTEST b
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
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)
This removes BI_BSELATEST_LATESTPRICESTEST from the sub-query and instead tells the database to use the columns from the table that it is updating to filter the sub-query. As you had written it, the database had no way of knowing how to correlate the columns from the sub-query to the table being updated.
精彩评论