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

Posted by Aglystas on Stack Overflow See other posts from Stack Overflow or by Aglystas
Published on 2010-05-28T17:52:19Z Indexed on 2010/05/28 18:02 UTC
Read the original article Hit count: 234

Filed under:

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.

© Stack Overflow or respective owner

Related posts about mysql