Convert existing custom update Query to Enhanced Query Execution

[madans131295]

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;

[rushikeshvartak · Accepted solution]

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;

[madans131295]

Thanks Rushikesh...