[mysql] UPDATE-JOIN

I used this query to identify duplicated records:

SELECT date, user_id, record_id
FROM records
WHERE active=1
GROUP BY date, user_id
HAVING COUNT(*)=2

Say it returns 26 records. Now I want to run a query to update these records' active column to zero. Simple, just nest the above as an outer query's IN clause, right?

UPDATE records
SET active=0
WHERE record_id IN (
  SELECT record_id
  FROM records
  WHERE active=1
  GROUP BY date, user_id
  HAVING COUNT(*)=2);

Nope. MySql has a little something to say about that plan.

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

Great, you can't nest a SELECT of the table you're updating. Fortunately, there is a way out, rewriting the above as an UPDATE-JOIN.

UPDATE records a 
JOIN (
  SELECT record_id
  FROM records
  WHERE active=1
  GROUP BY date, user_id
  HAVING COUNT(*)=2) b 
ON a.record_id=b.record_id
SET a.active=0;