Home » SQL & PL/SQL » SQL & PL/SQL » Update Statement Correction
Update Statement Correction [message #656656] Thu, 13 October 2016 10:17 Go to next message
suji6281
Messages: 49
Registered: September 2014
Member
Hi Team,

Could you please help me with Update SQL statement with the help of attached SELECT SQL statement.
Attachment has both select and update statement. Select Statement returning 2 rows with in seconds where as Update statement with same where clause of select statement but it is taking long time to complete.

Please correct me attached update statement. Thank You.

Regards
Smad

SELECT DISTINCT( b.transactionid ), 
               b.eoawthread_id, 
               c.oprid, 
               a.business_unit, 
               a.req_id 
FROM   psworklist c 
       JOIN ps_eoaw_wl b 
         ON b.busprocname = c.busprocname 
            AND b.activityname = c.activityname 
            AND b.eventname = c.eventname 
            AND b.worklistname = c.worklistname 
            AND b.instanceid = c.instanceid 
            AND b.transactionid = c.transactionid 
       JOIN ps_pv_req_aw a 
         ON a.eoawprcs_id = b.eoawprcs_id 
            AND a.eoawdefn_id = b.eoawdefn_id 
            AND b.eoawthread_id = a.eoawparent_thread 
            AND a.eoawprcs_id = b.eoawprcs_id 
            AND a.eoawdefn_id = b.eoawdefn_id 
            AND a.eoawthread_status IN ( 'A', 'C', 'D', 'N', 
                                         'T', 'S' ) 
       JOIN ps_req_line l 
         ON a.req_id = l.req_id 
            AND a.business_unit = L.business_unit 
            AND l.curr_status <> 'P' 
            AND c.inststatus = 1 
            AND c.busprocname = 'EOAW_APPROVALS' 
            AND c.activityname = 'EOAW_ROUTE' 
            AND a.req_id = '0003106027' 
            AND A.business_unit = 'PS001'

Output:
transactionid eoawthread_id oprid business_unit req_id
3098587 26294531 U271895 PS001 0003106027
3098588 26294531 A173236 PS001 0003106027

UPDATE psworklist 
SET    psworklist.inststatus = 2 
WHERE  EXISTS (SELECT DISTINCT C.transactionid 
               FROM   psworklist c 
                      JOIN ps_eoaw_wl b 
                        ON b.busprocname = c.busprocname 
                           AND b.activityname = c.activityname 
                           AND b.eventname = c.eventname 
                           AND b.worklistname = c.worklistname 
                           AND b.instanceid = c.instanceid 
                           AND b.transactionid = c.transactionid 
                      JOIN ps_pv_req_aw a 
                        ON a.eoawprcs_id = b.eoawprcs_id 
                           AND a.eoawdefn_id = b.eoawdefn_id 
                           AND b.eoawthread_id = a.eoawparent_thread 
                           AND a.eoawprcs_id = b.eoawprcs_id 
                           AND a.eoawdefn_id = b.eoawdefn_id 
                           AND a.eoawthread_status IN ( 'A', 'C', 'D', 'N', 
                                                        'T', 'S' ) 
                      JOIN ps_req_line l 
                        ON a.req_id = l.req_id 
                           AND a.business_unit = L.business_unit 
                           AND l.curr_status <> 'P' 
                           AND c.inststatus = 1 
                           AND c.busprocname = 'EOAW_APPROVALS' 
                           AND c.activityname = 'EOAW_ROUTE' 
                           AND a.req_id = '0003106027' 
                           AND A.business_unit = 'PS001')


[mod-edit: contents of attachment inserted into post by bb]

[Updated on: Thu, 13 October 2016 20:48] by Moderator

Report message to a moderator

Re: Update Statement Correction [message #656659 is a reply to message #656656] Thu, 13 October 2016 10:41 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that your query is doing what you want it to do? Not updating every row?



Re: Update Statement Correction [message #656661 is a reply to message #656656] Thu, 13 October 2016 10:53 Go to previous messageGo to next message
mist598
Messages: 1161
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,

what is your versions?
Re: Update Statement Correction [message #656662 is a reply to message #656656] Thu, 13 October 2016 11:35 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your UPDATE statement updates ALL rows of psworklist as soon as the EXISTS part returns TRUE.

Previous Topic: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month
Next Topic: Confirming Custom Raised Error Exceptions
Goto Forum:
  


Current Time: Sun Feb 25 09:07:26 CST 2018

Total time taken to generate the page: 0.02359 seconds