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

This error message typically occurs in MySQL when you try to update a table while using the same table in the subquery's FROM clause. MySQL doesn't allow this behavior as it can lead to unexpected results or conflicts in the data.

To resolve this error message, you can use a temporary table or a subquery to perform the update. Here's an example:

UPDATE r SET column1 = 'new value'
WHERE r.id IN (
SELECT id FROM (SELECT id FROM r) AS temp
);

In this example, we're creating a temporary table using a subquery and then updating the main table using the temporary table.

Alternatively, you can also use a JOIN to update the table. Here's an example:

UPDATE r
JOIN (
SELECT id FROM r
) AS temp ON r.id = temp.id
SET r.column1 = 'new value';

In this example, we're joining the main table with the subquery's result set and updating the matched records.