I have rows over 60000 in my table. How can I delete rows from 40000 to 50000 at once?
Another way to delete multiple rows is to use the IN operator. DELETE FROM table_name WHERE column_name IN (value 1, value 2, value 3, etc...); If you want to delete all records from the table then you can use this syntax.
To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.
Select the items you'd like to delete by pressing and holding the Shift or Command key and clicking next to each file/folder name. Press Shift to select everything between the first and last item.
You can use the between
function:
delete from exampleTable where id between 40000 and 50000
or:
delete from exampleTable where id >= 40000 and id <= 50000
pretty simple?
I'm assuming you want to do this based on the the default order of the table, and you don't have a natural key or some other way to order these in the table data.
I.e. SELECT * FROM WORDS
returns:
Hi Hello Bye Goodbye What's up
So if you said you wanted to delete rows 2 to 4, do you want to delete Hello, Bye, and Goodbye. Right?
Is the return order of your table deterministic? In other words, to you get the results in the same order every time? Ideally, you would be able to identify a column in your table that contains data you could key off of, which would let you use Tricker's answer.
If that isn't true, you will need to use the row number for your between statement. I'm not a mysql user, the code below would undoubtedly be cleaner if I were. Additionally, I don't even have a mysql database to test the query against. I put together a sql statement using this post as a reference for how to get a row num in mysql. With MySQL, how can I generate a column containing the record index in a table?
My query assumes you have some kind of primary key on your table. If not, there is no way to guarantee that you wont delete extraneous rows, plus it's good table design.
DELETE FROM YOUR_TABLE WHERE primarykey = (SELECT primarykey FROM (SELECT t.primarykey, @curRow := @curRow + 1 AS row_number FROM YOUR_TABLE t JOIN (SELECT @curRow := 0) r) WHERE row_number between 40000 and 50000);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With