Error Code: 1093. You can't specify target table 'PR_1' for update in FROM clause

Question

I'm trying to do an update:

UPDATE PR PR1
SET PR_KEY = 1
WHERE NOT EXISTS (
    SELECT 1
    FROM PR PR2 
    WHERE PR2.a = PR1.a ABD PR2.b = PR1.b
);

But I get this error:

Error Code: 1093. You can't specify target table 'PR1' for update in FROM clause.

Most solutions seem to focus on doing a sub-query but not sure how I can apply that here or maybe there's just a better way of getting what I want.

Answer 1

Use join instead of sub query

UPDATE PR PR1 
LEFT JOIN PR PR2 ON PR2.a = PR1.a AND PR2.b = PR1.b
SET PR1.PR_KEY = 1
WHERE PR2.a IS NULL