Update values from a nested select

[Tinnin]

Hi all,

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?

Thanks.

[pritaeas]

Does this not work?

UPDATE A SET Active = 0 WHERE A.NAME IN (
    SELECT A.Name
    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
)

Do you get an error?

[Tinnin]

Yeah. This was one of my earlier efforts.

[Err] 1093 - You can't specify target table 'A' for update in FROM clause

[pritaeas]

http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

[Tinnin]

Thanks Pritaeas. That might be just what I'm looking for.