Solved

Update Statement for multiple rows (follow-up)

Posted on 2004-08-31
8
1,913 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
Comment Utility
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
Comment Utility
The multiple rows return different values.
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 40 total points
Comment Utility
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
 
LVL 10

Accepted Solution

by:
SDutta earned 200 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Author Comment

by:ftaco96
Comment Utility
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 60 total points
Comment Utility
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 200 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now