Update multiple rows in oracle

Question

Hi! 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

Answer 1

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.

Answer 2

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.