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.

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?

Yeah. This was one of my earlier efforts.

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.