Update Statement Correction [message #656656] |
Thu, 13 October 2016 10:17 |
|
suji6281
Messages: 135 Registered: September 2014
|
Senior 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
|
|
|
|
|
|