MySQL - How to delete rows where column does not contain certain values?

Question

I'm trying to delete all rows where a specific column does not contain certain strings. Like this:

DELETE * FROM table
WHERE Disease NOT LIKE (Malaria, HIV, E. coli O157);

I get this error: #1064 - You have an error in your SQL syntax; near '* FROM table WHERE Disease NOT LIKE (Malaria' at line 1

Answer 1

If the description given is the exact description of the disease, just use NOT IN (with proper quotes):

DELETE FROM table
WHERE Disease NOT IN ('Malaria', 'HIV', 'E. coli O157');

but if it is a substring, you have to use multiple LIKE:

DELETE FROM table
WHERE
  NOT (Disease LIKE '%Malaria%'
       or Disease LIKE '%HIV%'
       or Disease LIKE '%E. coli O157%');