Solved

Update Statement for multiple rows (follow-up)

Posted on 2004-08-31
8
1,915 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 40 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
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 10

Accepted Solution

by:
SDutta earned 200 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 60 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 200 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

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.

Question has a verified solution.

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

Suggested Solutions

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

773 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