You can't specify target table 'user' for update in FROM clause

This error usually occurs when you try to perform an UPDATE query that references the table being updated in a subquery. To fix this, you can restructure your query to avoid referencing the table being updated in the subquery.

Here's an example of the error you mentioned:

UPDATE user
SET status = 'active'
WHERE user_id IN (
    SELECT user_id
    FROM user
    WHERE created_date < '2021-01-01'
);

To resolve this issue, you can rewrite the query using a JOIN:

UPDATE user
JOIN (
    SELECT user_id
    FROM user
    WHERE created_date < '2021-01-01'
) AS subquery ON user.user_id = subquery.user_id
SET user.status = 'active';

By using a JOIN, you can update the desired records without referencing the target table in the subquery.