0

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.

2
Contributors
4
Replies
21
Views
4 Years
Discussion Span
Last Post by Tinnin
0

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?

Edited by pritaeas

0

Yeah. This was one of my earlier efforts.

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.