SQL delete if id does not match array

Question

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?

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
– gurkan, Commented Apr 5, 2021 at 19:11
Does this answer your question? How to delete rows in MYSQL where a column doesn't contain certain data — Connor Low
– Connor Low, Commented Apr 5, 2021 at 19:13

Gordon Linoff · Accepted Answer · 2021-04-05 19:04:35Z

3

You want not in:

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

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

answered Apr 5, 2021 at 19:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nguyễn Văn Phong Over a year ago

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. stackoverflow.com/help/someone-answers

Collectives™ on Stack Overflow

SQL delete if id does not match array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related