0

Is there an elegant way to delete all non-selected entries from a table after selecting from it?

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
Hamster
  • 2,962
  • 7
  • 27
  • 38

2 Answers2

1

I think you mean DELETE and not DROP. DROP can only erase an entire object (e.g., TABLE). If you aren't worried about performance, you can have something like

DELETE FROM mytable WHERE mytable_key NOT IN 
  (SELECT mytable_key FROM mytable WHERE some_or_another_condition);

Many DBs allow a JOIN-type syntax that will probably perform better if you have to do this on a frequent basis.

Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53
  • Would MYSQL have such a function? – Hamster May 23 '11 at 20:12
  • I don't think that will work in mysql due to the restriction on deleting and selecting from the same table in the same statement (http://dev.mysql.com/doc/refman/5.1/en/delete.html). But you can work around it by doing a nested subselect (see example at the bottom of http://bugs.mysql.com/bug.php?id=5037): – davek May 23 '11 at 20:18
  • @davek You appear to be correct: apparently in MySQL it is necessary to alias the subquery. That's substandard. Note to OP: That second link of davek shows the workaround. – Andrew Lazarus May 23 '11 at 20:25
0

Scary and generic statement... but this will do from whatever table... I wouldn't think you would actually do to a production table though...

delete from SomeTable where NOT (some condition)

DRapp
  • 47,638
  • 12
  • 72
  • 142