Check here: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html Look for the IN and NOT IN comparators :-) DELETE FROM the_table WHERE `ID` IN(1,2,3,4,5,6) Shawn Green Database Administrator Unimin Corporation - Spruce Pine -{ Rene Brehmer }- <metalbunny@xxxxxxxxxxxxxx> wrote on 11/02/2004 07:29:20 PM: > 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/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=sgreen@xxxxxxxxxx >
