I have the following query:
SELECT A.Name, MAX(B.VisitDate) AS 'Last Visit Date', A.Active FROM B INNER JOIN A ON B.NameCodeB = A.NameCodeA GROUP BY B.NameCodeB HAVING MAX(B.VisitDate) < DATE_SUB(CURDATE(),INTERVAL 2 MONTH) AND A.Active = 1
This selects a list of all people in the database who last visited over two months ago and are still active.
I am looking to UPDATE their active status to A.Active = 0 within the same query.
I have tried IN, WHERE EXISTS, nested SELECTS and a few other random things but I can't get it to work.
Does anyone have any suggestions?