Update Statement for multiple rows (follow-up)

I thought I got this question answered, but I jumped the gun a little...
http://www.experts-exchange.com/Databases/Oracle/Q_21110353.html#11946180

I ran into a snag, because the sub-query gets multiple rows. What I'm really looking to do is update all the rows in the detail table that correspond to the rows in the sub-query. SDutta says in the other question that it can only be done with a cursor, but I could've sworn I'd seen this in an update statement somewhere before.

Here's what the query looks like right now.

UPDATE DETAIL det
SET (rejects, modified_by, modify_date) =
(SELECT
   smy.REJECT, 'myid' as modified_by, sysdate as modify_date
FROM
   ACTUALS act
 , CYCLE cyc
 , SUMMARY smy
WHERE
     act.CYCLE_ID = cyc.CYCLE_ID
 AND smy.CYCLE_ID = cyc.CYCLE_ID
 AND smy.MONTH_YEAR = act.MONTH_YEAR
 AND cyc.MARKET_ID = iMarketID
 AND act.EVENT_ID = 32
 AND act.CYCLE_TYPE = 'M'
 AND act.actual_time < dtLastRpt
 AND act.actual_time > dtPrevRpt
)
WHERE EXISTS (select 'x' from SUMMARY smy
    WHERE det.CYCLE_ID = smy.cycle_id
    and det.MONTH_YEAR = smy.month_year);
LVL 9
ftaco96Asked:
Who is Participating?
 
SDuttaConnect With a Mentor Commented:
Hopefully some other expert will add their valuable suggestions. But as far as I know, a SELECT statement in a Stored Procedure cannot return more than 1 row unless it is a cursor, you have to limit it with a condition. Even a cursor can process only 1 row at a time within a loop.
0
 
SDuttaCommented:
As I mentioned in your previous question, you can add to the WHERE clause
AND ROWNUM=1

This will only solve the problem if the multiple rows will return the same values.
0
 
ftaco96Author Commented:
The multiple rows return different values.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
seazodiacConnect With a Mentor Commented:
How do you want it if the sub-query return multiple rows? you want to update with first_row or last_row? what's your selection criteria?
0
 
ftaco96Author Commented:
I need the query to update only those rows in det where there are corresponding rows in the summary table, which determines what rows the subquery brings back. Does that make any sense?
0
 
amit_chauhanConnect With a Mentor Commented:
The error you are getting is because of the subquery in the SET clause....
SELECT
   smy.REJECT, 'myid' as modified_by, sysdate as modify_date
FROM
   ACTUALS act
.........

The sub query in the SET clause should return only 1 row. Somehow your query is returning more than 1 row, and that's why Oracle is cribbing.

You need to restrict it so that it returns only 1 row. As SDutta mentioned, add rownum = 1 in the where clause.

The lower subquery is fine and should allow you update only those rows in detail table that satisfies the sub query condition.

Thanks
Amit
0
 
grant300Connect With a Mentor Commented:
The real problem you have is that you are not using fully corellated subqueries.

What you need to do is think of the statement as a large JOIN rather than trying to stuff a subquery in where it can't really be right.

In general, the query will be of the form:

UPDATE DETAIL
  SET rejects = smy.REJECT,
         modified_by = 'MYID',
         modified_date = sysdate
 FROM DETAIL det,
          ACTUALS act,
          CYCLE cyc,
          SUMMARY smy
WHERE act.CYCLE_ID = cyc.CYCLE_ID
    AND smy.CYCLE_ID = cyc.CYCLE_ID
    AND smy.MONTH_YEAR = act.MONTH_YEAR
    AND cyc.MARKET_ID = iMarketID
    AND act.EVENT_ID = 32
    AND act.CYCLE_TYPE = 'M'
    AND act.actual_time < dtLastRpt
    AND act.actual_time > dtPrevRpt
    AND det.CYCLE_ID = smy.cycle_id
    AND det.MONTH_YEAR = smy.month_year;

Now,  what you want to do is try this as a SELECT statement to see what the results are before you make it an UPDATE.  You will have to add the identifyting columns from each of the tables involved to the select list.  Run the query and make certain that you have only one resulting row for each row in DETAIL you intend to update.  If you have duplicates, you have failed to constrain the join tightly enough and need to add join and/or filter criteria until you get what you need.

Best of luck,
Bill


0
 
ftaco96Author Commented:
Thanks for your input everyone. I ended up using SDutta's suggestion from the previous question by using a cursor. If I ever get the time, I'll try yours, too grant300.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.