MySQL: delete all rows not just updated

问题:

The idea is that after looping over a submission form, all rows not just updated by the form are deleted from the database.

Is there an elegant MySQL declaration for something like that?

回答1:

No. Store the PKs and use NOT IN after to delete.

回答2:

Deleting before hand is dangerous; what if a user closes the browser? Aid storing primary keys is an option. The simplest ended up being this:

1) Set all matching row dates to something like Jan 1 1970. 2) Submit form, including current time in updated rows. 3) Delete all rows with old date.

Works very well.

回答3:

you could have a timestamp column in the table. all rows that are updated would have the current timestamp. and then you can run a delete query selecting all rows that would have their timestamps less than the approximate time when you updated the table. not a full-proof solution though.

another would be to set some columns value while updating, and later deleting the rows whose column value did not get updated

回答4:

How about a reversed condition?

Update tbl set this = that where col = 'something'; Delete from tbl where col != 'something';