0

I have a table testing with two columns

  • id: auto_increment
  • name: varchar(20)

I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records from MySQL table (InnoDB engine). How can I achieve this?

This table I have created is just for testing purposes. My actual table contains billions of records. One way I thought as:

delete from testing where id in (select group_concat(id) from testing limit 1, 2);

I think this would be quite slow in case of my actual table.

Can anyone provide any better solution?

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Kamal Nayan
  • 153
  • 3
  • 11
  • 4
    There is no "middle", "2nd" or "3d" record in a relational table, so your problem statement amounts to deleting two random rows from the table. Is that what you want? – mustaccio Apr 20 '16 at 16:01
  • N rows starting from a random row. In this example, 2 rows starting from 2nd row. It could be 2 Million rows starting from 10th row. – Kamal Nayan Apr 20 '16 at 17:32
  • 2
    "starting from the 2nd row, starting from the 10th row." You did not understand mustaccio's comment. There is no inherent order in SQL tables. When you say "second row", second by which order do you mean? (it can order by id, order by name, order by name desc, etc...) LIMIT without ORDER BY will essentially give some random results. – ypercubeᵀᴹ Apr 20 '16 at 18:42
  • Refer question once again, "I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records". So, Its clear that I want to delete only those records that are fetched from the select query. I think by default order by is applied on first column, which is an auto_increment id here. Am I clear now? – Kamal Nayan Apr 21 '16 at 07:17

1 Answers1

0

What you are asking for can lead to unpredictable results in certain situations.

What kind of situations?

  1. Running this on a Master and the Slave receives the same SQL. The problem I see is that rows of data in a Master may not be physically written in the same order on the Slave.
  2. Triggers will encounter issue (or really create issues) with this type of DELETE query

I am sure there are other situations, but these came to mind first.

I addressed these before

SUGGESTION

If you look back at the original question (Problem with MySQL subquery), the OP suggested getting the key for the row you want deleted. Then issue the delete using that key. It's the safest way.

YOUR ACTUAL QUESTION

Doing fancy DELETEs, even if possible, could produce unwantd results on Slave, on a Master, or even a Standalone DB Server when not using WHERE.

In the end, you must use WHERE for the safest and most expected results.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520