Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/20/2024 06:49 AM
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;
Solved! Go to Solution.
05/20/2024 09:35 AM
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;
05/21/2024 05:23 AM
Thanks Rushikesh...