Solved

PL/SQL Update Complex Statement

Posted on 2013-05-16
4
540 Views
Last Modified: 2014-02-22
Date-Completed-Script-for-item-n.txtI need to perform a complex update statement in Oracle where each record that needs to be updated can have a different date.   I tried the straight SQL route using a single update statement that is not loop based.  I think it could work but the statement alone took 1 hr to run.

I am looking for a better solution using PL/SQL Loop and may have it but not sure and hope someone can review my code for errors.
0
Comment
Question by:gilnari
  • 3
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39171456
You do not need, and should not use, 'distinct' in a query that is using 'group by'
distinct is completely useless with a group by

Don't use to_char() on those date fields

You seem to have too many fields in the query and the group by for the needs of the update
e.g.
SELECT
      cc.item_number
    , MAX(aa.TIMESTAMP) AS maxdate
FROM instance_history@location.com aa
    , user.result_mapping cc
    , user.sample dd
WHERE aa.object_id = cc.old_itemnumber
    AND cc.item_number = dd.item_id
    AND aa.TIMESTAMP <> dd.date_completed
    AND aa.final_state = 'COMPLETE'
    AND dd.STATUS IN ('A','C')
GROUP BY cc.item_number;

UPDATE user.sample dd
SET dd.date_completed = c_select_task_complete_date.maxdate
WHERE cc.item_number = dd.item_id;

Open in new window

How long does it take to run just the query above? Don't see how using a cursor is going to enhance performance.
0
 

Author Comment

by:gilnari
ID: 39171612
there are issues with the date comparison if I don't use TO_CHAR.   I can try it without but I do need to define the date as mm/dd/yyyy.  As you can see I am calling from other database to get to get the old date and its not in the same format as the date in the new system.


I use distinct because I have tried it without and get duplication of values.  Long story on that one.  If I can leave it let's.

The date for each record where cc.item_number  = dd.item_id can be different and/or I have multiply dates and I need the max date for each record where cc.item_number  = dd.item_id

As example
1234AB can have a date of 03/04/2012
1234AB can have a date of 02/15/2012
4455CC can have a date of 06/07/2011
4455cc can have a date of 04/04/02012

the update need to update
1234AB to 03/04/2012
4455CC to 06/07/2012

The query does work as original built, my bigger concern is the update statement executing across all the records correctly  apply the date unique to that record.   The changes above I see took out the loop.  Don't you need a loop to check for the specific record date value and apply it and is the PL/SQL statement that original had will it work?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39173245
>>I use distinct because I have tried it without and get duplication of values
distinct is useless if you are using group by
group by (by definition) produces unique rows, it has to do this so that the aggregate functions work. distinct should NEVER be used with group by.

There is a tendency for people to use the word distinct in a non-technical way, distinct does absolutely nothing that group by does not do. If you used a group by query that produced "unwanted repitition" then there are probably too many fields in the group by clause.

>>I do need to define the date as mm/dd/yyyy
why?
dates are NOT stored as strings, in Oracle dates are in fact stored as a set of 7 integers.
mm/dd/yyyy is a format mask for presentation of dates to humans

plus:
mm/dd/yyyy is an ambiguous format
yyyy-mm-dd at least is not ambiguous

>>there are issues with the date comparison if I don't use TO_CHAR
The only date comparison is below and that does not use to_char()
AND aa.TIMESTAMP <> dd.date_completed

If the need is to update dd.date_completed to a whole date, without time, then you should use TRUNC()
TRUNC(MAX(a.TIMESTAMP)) AS maxdate

>>the update need to update
>>1234AB to 03/04/2012
>>4455CC to 06/07/2012


then that's what should be in the query, just cc.item_number & max(aa.timestamp)

>> Don't you need a loop to check for the specific record
NOT IF THIS THE JOIN IS ACCURATE

WHERE cc.item_number = dd.item_id;

anyway, where's the loop? all you have between that begin/end is one update and one dbms_output, it's not looping. It relies on the join to match the records.

BEGIN
UPDATE user.sample dd
SET dd.date_completed = c_select_task_complete_date.maxdate
WHERE cc.item_number  = dd.item_id;

DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

END;

that begin/end is much the same as this:
WITH SQ as (
            SELECT
                  cc.item_number
                , TRUNC(MAX(aa.TIMESTAMP)) AS maxdate
            FROM instance_history@location.com aa
                , user.result_mapping cc
                , user.sample dd
            WHERE aa.object_id = cc.old_itemnumber
                AND cc.item_number = dd.item_id
                AND aa.TIMESTAMP <> dd.date_completed
                AND aa.final_state = 'COMPLETE'
                AND dd.STATUS IN ('A','C')
            GROUP BY cc.item_number
            )
UPDATE user.sample dd
SET dd.date_completed = SQ.maxdate
WHERE SQ.item_number = dd.item_id;

Open in new window

You may choose to use a cursor, and that's fine, but using the cursor isn't going to improve performance. Tuning that query will have a bigger impact.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281288
gilnari,
Hi. just wondering, have you been able to perform that update now?
is this question complete?
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

757 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

13 Experts available now in Live!

Get 1:1 Help Now