best way to delete 3000+ items from multiple tables

what is the best way to delete moe that 3000 items from multiple tables

[Wayder]

I need to delete about 3000 items from multiple tables. At the moment I use the the following query for low quantities.

$query = "DELETE products
,join_cats_products
,join_manufacturers_products
FROM products
,join_cats_products
,join_manufacturers_products
WHERE products.prod_id=join_cats_products.prod_id
and products.prod_id=join_manufacturers_products.prod_id
and products.prod_id='20'
";

Would it be better to repeat the above query 3000 times or use the following

$query = "DELETE products
,join_cats_products
,join_manufacturers_products
FROM products
,join_cats_products
,join_manufacturers_products
WHERE products.prod_id=join_cats_products.prod_id
and products.prod_id=join_manufacturers_products.prod_id
and (products.prod_id='20' OR products.prod_id='20' etc. 3000 times)
";

Any other suggestions would be gratefully received.

Thanks

Ray...

[henry0]

Shall we understand that your productID will be a var?
if so I will find a way to select and load those id in a temp table
get a num of id and itterate through the id list

but again I am not sure that I understood your examples.

<edit>more about it</edit>
if you do not use InnoDB engine I willl first ALTER concerned tables to InnoDB engine
and use transactions; one never know what could go wrong :)

[whoisgregg]

A more efficient way of writing the last bit of code is using the IN statement:

AND `products`.`prod_id` IN ('20','25','26', [...] )

The only limit to how many items you can process at once using this method is only limited to the maximum size of a SQL query (measured in bytes, not characters, and you can find that by running

SHOW VARIABLES LIKE 'max_allowed_packet';

).

[Wayder]

Worked like a charm.

Thank you.