How to Delete Multiple Rows in MySQL?

Struggling with Multiple Row/Category Deletion

[jimh009]

Hello,

I'm new to PHP/MySQL but have made it to first base at least.

However, this little problem has me stumped. Hoping someone can help me out.

I have a table (very large one) that contains about 200 categories and is 32,000 rows long, more or less. I need to "trim" this table down to only the categories that I want - which is about 20 or so. In essence, I want to delete everything out of this table that is not needed. Unfortunately, going about this is causing what little hair I have left to disappear rather quickly.

I'm able to delete all the rows of a category from the table one at a time using the following command:

DELETE FROM table_name WHERE category = "long_category_name1";

I've found this also works too:

DELETE FROM table_name WHERE category = 'long_category_name1';

My problem comes in attempting to delete multiple categories from the same SQL Delete Statement. I've tried darn near everything and nothing works. I know there must be a simple way to "string" things together in the WHERE statement (thus having a long_category_name2, long_category_name3, etc..., but I've yet to figure it out.

Anyone want to help out?

Jim

[jatar_k]

DELETE FROM table_name WHERE category IN ('long_category_name1', 'long_category_name2', 'long_category_name3');

[jimh009]

Thank you Jatar. That worked like a charm.

I'm curious, what does 'IN' do? I haven't come across that in my studies yet.

Jim

[syber]

The IN predicate is just shorthand for entering several OR statements.

Instead of entering

WHERE category = 'long_category_name1' OR
category = 'long_category_name2' OR
category = 'long_category_name3' OR
category = 'long_category_name4'

you can say

WHERE category IN ('long_category_name1', 'long_category_name2', 'long_category_name3', 'long_category_name4')

The IN predicate will work with any datatype

[jatar_k]

there is also BETWEEN (allows for a range), they are both always very hard to find in the manuals

[coopster]

Indeed, they are. Both are typically found in the Comparison Operators and Functions [dev.mysql.com] pages of SQL manuals.