Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Convert existing custom update Query to Enhanced Query Execution

madans131295
New Contributor
New Contributor

Can anyone please help to rewrite the below update query into Enhanced Query Execution (Update Operation).

I got stuck with 'Inner Join' and also 'Group By' clause

Query is: -

UPDATE arstasks at
INNER JOIN
(SELECT
at1.taskkey,
at1.requestkey AS requestid,
ra.parentrequest AS prequest
FROM
ars_requests ar, request_access ra, arstasks at1, endpoints ed
WHERE
ar.REQUESTKEY = at1.REQUESTKEY
AND ar.REQUESTKEY = ra.REQUESTKEY
AND ed.endpointkey = at1.endpoint
AND at1.tasktype = 3
AND at1.requestaccesskey = ra.parentrequest
AND ed.endpointkey IN ()
AND at1.status = 1
AND ar.status = 3
AND ra.accesstype = 7
GROUP BY ra.parentrequest
HAVING SUM(ra.status = 4) > 0
AND SUM(ra.status = 3) = 0) AS res ON res.requestid = at.requestkey
AND res.prequest = at.requestaccesskey
SET
at.status = 4
WHERE
at.status = 1;

2 REPLIES 2

rushikeshvartak
All-Star
All-Star

SELECT at.taskkey as arstasks__primarykey,4 as arstasks__status
FROM arstasks at
INNER JOIN
(
SELECT
at1.taskkey,
at1.requestkey AS requestid,
ra.parentrequest AS prequest
FROM
ars_requests ar
JOIN request_access ra ON ar.REQUESTKEY = ra.REQUESTKEY
JOIN arstasks at1 ON ar.REQUESTKEY = at1.REQUESTKEY
JOIN endpoints ed ON ed.endpointkey = at1.endpoint
WHERE
at1.tasktype = 3
AND at1.requestaccesskey = ra.parentrequest
AND ed.endpointkey IN ()
AND at1.status = 1
AND ar.status = 3
AND ra.accesstype = 7
GROUP BY ra.parentrequest
HAVING SUM(ra.status = 4) > 0
AND SUM(ra.status = 3) = 0
) AS res
ON res.requestid = at.requestkey
AND res.prequest = at.requestaccesskey
WHERE at.status = 1;


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

madans131295
New Contributor
New Contributor

Thanks Rushikesh...