0

I have a SQL table that looks like this

ID | name | age
---------------
1  | john   | 22
2  | adam   | 33
3  | joe    | 43
4  | paul   | 23
1  | peter  | 44
2  | simon  | 67
1  | rod    | 32
7  | aaron  | 15
3  | tom    | 55

I am trying to delete everything that does not have an id of either 1,3,4

So I should be left with

ID | name | age
---------------
1  | john   | 22
3  | joe    | 43
4  | paul   | 23
1  | peter  | 44
1  | rod    | 32
3  | tom    | 55

I have this so far but it is not working

DELETE FROM `mytable` WHERE `id` != 1,3,4;

Where am I going wrong?

jsmitter3
  • 411
  • 1
  • 4
  • 15
  • I'm just asking out of curiosity. How inserted the duplicate rows to `Id` column and how do you seperate them even after delete query was executing. Is not that _primary key_? – gurkan Apr 05 '21 at 19:11
  • Does this answer your question? [How to delete rows in MYSQL where a column doesn't contain certain data](https://stackoverflow.com/questions/30825042/how-to-delete-rows-in-mysql-where-a-column-doesnt-contain-certain-data) – Connor Low Apr 05 '21 at 19:13

1 Answers1

3

You want not in:

DELETE FROM `mytable`
    WHERE `id` NOT IN ( 1, 3, 4 );

!= -- or more commonly <> is used only for single values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Welcome, @jsmitter3 to Stackoverflow. I saw that you forgot to mark the answers as resolved by clicking the tick to help the reader in the future. https://stackoverflow.com/help/someone-answers – Nguyễn Văn Phong Apr 14 '21 at 01:28