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 }- <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>
