CODEWITHSUNDEEP
mysql
Rita Yoko
Rita Yoko

Reputation: 1

Delete all records except some id?

I have table "table_film" and column "film_cat" as picture below

film_cat

I want to delete all id in film_cat Except id 4,9,114,131 ( they don't include 4 number at same time, maybe ,4,9, - ,4,114, - ,4,9,131,... )

I tried but not work !

DELETE FROM `table_film` WHERE id NOT IN (SELECT * FROM table_film WHERE film_cat = '4' or film_cat='9' or film_cat='114' or film_cat='131')

DELETE FROM table_film where film_cat NOT IN (4,9,114,131)

DELETE FROM table_film WHERE film_cat != '4,9,114,131'

Upvotes: 0

Views: 1225

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

That's what happens when you have a really, really poor data representation. You should be using a junction table, film_categories with one row per film and per category.

Storing multiple ids in a single column is a bad idea. Why is it bad?

  • Storing multiple values in a single column is bad.
  • Storing numbers as strings is bad.
  • Having undeclared foreign key relationships is bad.
  • MySQL has poor string manipulation functions.

So, you should fix the data model.

Now, sometimes we are stuck with other people's really bad design deicsions. Here is one method that you can use in that case:

DELETE FROM table_film 
    WHERE find_in_set(4, film_cat) > 0 AND
          find_in_set(9, film_cat) > 0 AND
          find_in_set(114, film_cat) > 0 AND
          find_in_set(131, film_cat) > 0 ;

Note: The ANDs perhaps should be ORs. The question is unclear on the exact logic.

Upvotes: 3

Blank
Blank

Reputation: 12378

Try this;)

DELETE FROM table_film WHERE film_cat NOT REGEXP '4|9|114|131'

DELETE FROM table_film WHERE film_cat NOT REGEXP '[[:<:]]4[[:>:]]|[[:<:]]9[[:>:]]|[[:<:]]114[[:>:]]|[[:<:]]131[[:>:]]'

Or use FIND_IN_SET:

DELETE FROM table_film
WHERE FIND_IN_SET('4', film_cat) = 0
AND FIND_IN_SET('9', film_cat) = 0
AND FIND_IN_SET('114', film_cat) = 0
AND FIND_IN_SET('131', film_cat) = 0

Upvotes: 0

Related Questions