CODEWITHSUNDEEP
mysqlsqlsql-delete
PJ_
PJ_

Reputation: 31

Delete all rows except specifics

I have a large database with 300,000 rows (1.6 GB). I need to delete them all EXCEPT the ones that has the following features: 

main_sec=118 
main_sec=Companies
type=SWOT

Here is the code I prepared, but somehow, it's deleting all the rows of the table:

DELETE FROM `swots` 
WHERE (main_sec <> '118') OR
      (main_sec <> 'Companies') OR 
     (type <> 'SWOT');

Please help me to understand where the mistake is.

Upvotes: 1

Views: 78

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Try this:

DELETE FROM `swots` 
WHERE (main_sec not in ('118', 'Companies')) OR
      (type <> 'SWOT');

The problem is that main_sec is always not equal to one of those two values in a given record. So, every record meets the where condition in your version.

Upvotes: 1

Stephan
Stephan

Reputation: 8090

It would be faster to insert the rows that you want to keep (assuming they are fewer then the remaining rows) in a new table like :

INSERT INTO main_sec_new
SELECT
   *
FROM main_sec 
WHERE main_sec IN ('118','Companies')
and type = 'SWOT'

And then just drop the old table

Upvotes: 1

juergen d
juergen d

Reputation: 204746

DELETE FROM `swots` 
WHERE main_sec not in ('118', 'Companies')
and type <> 'SWOT'

Upvotes: 2

Related Questions