How to delete all but 2 specific items in MYSQL

Question

I am trying to remove all users from mysql.user except users "1" and "2";

I executed this command, but also deleted the user "1" and "2";

mysql> delete from mysql.user where user!='1' or user!='2'; 

I also tried

  mysql> delete from mysql.user where (user!='1' or user!='2'); 

same result, user '1' and '2' are deleted.

Answer 1

This should work with NOT IN :

 DELETE FROM mysql.user where user NOT IN (1,2) 

The problem with your request is OR - it removes the other because 1 is not equal to 2 and vice versa.

Your request will work with AND :

 delete from mysql.user where (user!='1' AND user!='2'); 

SQL Fiddle Demo

vs

SQL Fiddle Demo with or

Answer 2

You can use NOT IN

 DELETE FROM mysql.user WHERE user NOT IN (1, 2) 

The reason both are turned on is simple. 1 <> 2 and 2 <> 1 .

Answer 3

delete from mysql.user where the user is not in ('1', '2')