Oracle script for getting results and update a table column in the same time

Question

I would like your help for creating a script for getting results and in the same time updating a field in my table if necessary.

In my application, I have persons (table PERSON) who create REQUESTS (table REQUEST). A person is active when she has created a request during the last 3 years. I have created a field (ACTIVE - default value: 1) in the table PERSON in order to know if the person is still active.

I create a query for retrieving the number of requests for each person (Total request number, active request, inactive request):

-- PERSONS List with number of request for each person and RE_ACTIVE field
SELECT p.id,
       p.lastname || ' ' ||  p.firstname personname,
       p.company,
       p.active,
       (SELECT count(*)
        FROM request req
        WHERE req.personid = p.id) total_request_nb,
       (SELECT count(*)
        FROM request reqact
        WHERE reqact.personid = p.id
        AND reqact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)) nb_active_requests,
       (SELECT count(*)
        FROM request reqinact
        WHERE reqinact.personid = p.id
        AND reqinact.requestdate < add_months(trunc(sysdate, 'YYYY'), -36)) nb_inactive_requests,
       CASE
         WHEN EXISTS (SELECT *
                      FROM request reqreact
                      WHERE reqreact.personid = p.id
                      AND reqreact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)) 
         THEN 1 
         ELSE 0
       END re_active
FROM person p;

This script is working. I would like to update the field ACTIVE when the person is active (with the previous result). For instance:

UPDATE PERSON p SET ACTIVE =     
CASE  WHEN (
    (SELECT count(*)
     FROM request reqreact
      WHERE reqreact.personid = p.id
     AND reqreact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)) > 0
    )
    THEN 1 
    ELSE 0  
END   

I would like to know if it's possible to do that in the same script? Hence I could know how many updates have been done, failed, ... in once query.

Thanks you in advance for your help

Answer 1

You want a WHERE EXISTS condition with a correlated subquery :

UPDATE PERSON p
SET p.ACTIVE = 1
WHERE EXISTS (
    SELECT 1
    FROM request reqreact
    WHERE reqreact.personid = p.id
    AND reqreact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)
)

If there is no match in the subquery, the UPDATE in the outer query will not happen.

If you want to set to 1 or 0 depending on the result of the subquery :

UPDATE PERSON p
SET p.ACTIVE = CASE 
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM request reqreact
            WHERE reqreact.personid = p.id
            AND reqreact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)
        ) 
        THEN 1
        ELSE 0
    END