?
Solved

Update Statement for multiple rows (follow-up)

Posted on 2004-08-31
8
Medium Priority
?
1,932 Views
Last Modified: 2008-01-09
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);
0
Comment
Question by:ftaco96
8 Comments
 
LVL 10

Expert Comment

by:SDutta
ID: 11946509
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
 
LVL 9

Author Comment

by:ftaco96
ID: 11946552
The multiple rows return different values.
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 160 total points
ID: 11946583
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Accepted Solution

by:
SDutta earned 800 total points
ID: 11946618
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
 
LVL 9

Author Comment

by:ftaco96
ID: 11946645
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
 
LVL 2

Assisted Solution

by:amit_chauhan
amit_chauhan earned 240 total points
ID: 11947723
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
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 800 total points
ID: 11948074
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
 
LVL 9

Author Comment

by:ftaco96
ID: 12097124
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question