Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update Statement for multiple rows (follow-up)

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

790 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