CODEWITHSUNDEEP
mysql
Karadur
Karadur

Reputation: 1246

DELETE after SELECT - will mysql maintain the same order?

I've got some existing code with couple of queries and I'm trying to figure out if the functionality is correct. The queries are performed on a simple table where integer unique IDs are stored. So the code does the first query:

SELECT id FROM id_table LIMIT some_number;

then it does some operations on retrieved ids and removes them from the table:

DELETE FROM id_table LIMIT some_number;

From what I understand in mysql the order is not guaranteed (and no ORDER BY is specified in there). So will the second query delete the same records that were retrieved in the first query, given that nobody else does queries to id_table?

Thanks

Upvotes: 1

Views: 320

Answers (1)

Bohemian
Bohemian

Reputation: 425258

You shouldn't do that. The fundamental problem is that unless you use an order by clause, there is no guarantee of the rows returned/deleted using limit.

You should use an order by, in both select and delete, on the same column(s), like this:

SELECT id from id_table order by id LIMIT some_number;

DELETE from id_table order by id LIMIT some_number;

You can order by anything you want as long as it's consistent, but id would be the logical choice for an auto_increment column.

Upvotes: 2

Related Questions