DELETE FROM the_table WHERE `ID` IN(1,2,3,4,5,6) will work just fine. The trick is to be sure that
those records indeed are to be deleted. I prefer to mark the record as deleted for a time before permanent deletion. That way its recoverable should something really bad happen.
bastien
From: -{ Rene Brehmer }- <metalbunny@xxxxxxxxxxxxxx> To: php-db@xxxxxxxxxxxxx,mysql@xxxxxxxxxxxxxxx Subject: Using an array(-ish) in SQL queries Date: Wed, 03 Nov 2004 01:29:20 +0100
X-posted to MySQL and PHP DB
Hi gang
Task at hand: deleting or selecting (same difference) several numbers of records using only 1 query.
My first version simply looped through all the ticked off IDs and ran a single query for each delete routine. I've still not suceeded in getting the delete queries to work on multiple tables at once, despite the column names being the same. But besides this:
My current version generates, for multi-select cases, queries like this:
DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR `ID`='5' OR `ID`='6'
or similar with the SELECT statement.
On some occasions this can result in a very large amount of OR statements, like for 50 IDs totally.
I've been reading through the MySQL manual and the comments in the select and delete parts, but cannot seem to find any mentioning of an easier way to do this. Or it's been deluting me cuz English is my second language, so the MySQL manual doesn't always make much sense to me.
I'm looking for something like passing on an array (as comma-seperated-list maybe), and then just do statements like:
DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6)
Did I totally miss that part of the manual, or is it just not possible with MySQL ?
Now, for my script it doesn't really matter much which approach to use, but was more thinking performance wise it ought to be faster and less taxing for the server to parse an SQL statement that's closer to table structure, rather than the OR statements that has to be transformed first.
Sorry if I'm just a blind mouse that can't seem to find things in the MySQL manual. It's not really my best friend...
TIA
Rene -- Rene Brehmer aka Metalbunny
If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile?
http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
