Best way to perform DELETE that uses ids from a SELECT statement in MYSQL

Question

I'm working on a stored procedure, that needs to delete specific rows based on a timestamp. Here's what I was going to use until I found out you can't include a select clause in the delete statement if they are both working on the same table.

DELETE FROM product WHERE merchant_id = 2 AND product_id IN 
(SELECT product_id FROM product WHERE merchant_id = 1 AND timestamp_updated > 1275062558);

Is there a good way to handle this within a stored procedure. Normally I would just throw the logic to build the product_id list in php, but I'm trying to have all the processing done on the data server.

Answer 1

Use a self-join.

DELETE p0
FROM product AS p0
JOIN product AS p1 ON p1.product_id=p0.product_id
WHERE p0.merchant_id=2
AND p1.merchant_id=1 AND p1.timestamp_updated>1275062558