CODEWITHSUNDEEP
mysqlsql
Logan
Logan

Reputation: 61

MySQL Delete Query

I am having some trouble with a MySQL query. I have a field in a table called "ID", I want a query that deletes all rows in that table unless they have a certain ID, I want more to be able to define more than 1 ID in the query.

Any ideas?

Upvotes: 1

Views: 1620

Answers (6)

Jeremy W
Jeremy W

Reputation: 58

You should try this query:

DELETE FROM tablename WHERE ID NOT IN ('1','2','3');

Upvotes: 1

orif
orif

Reputation: 362

You can also use this with some extra conditions:

    delete from your_table_name where id not in (select id from your_table_name where necessary=false)

Upvotes: 1

Jakub Oboza
Jakub Oboza

Reputation: 5421

This should do the job

delete from TABLE_NAME where id not in (1,2,3,4,5,6)

of (1,2,3,4,5...) is a list of id's you want to save.

Upvotes: 1

Taryn
Taryn

Reputation: 247870

DELETE
FROM yourTable
WHERE yourID NOT IN (1,2,3,4) -- place your list here

Or of you do not want to list your ids, you can use a subquery which would contain the list of ids you want to keep:

DELETE
FROM yourTable
WHERE yourID NOT IN (SELECT * FROM yourTable WHERE ...)

Upvotes: 2

juergen d
juergen d

Reputation: 204924

delete from your_table where id not in (1, 2, 4)

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65587

DELETE FROM your_table
WHERE id NOT IN (1,2,3)

Upvotes: 3

Related Questions