asked on

PL/SQL Update Complex Statement

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.

PortletPaul

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.

gilnari

ASKER

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?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

gilnari,
Hi. just wondering, have you been able to perform that update now?
is this question complete?