SQL Delete Records within a specific Range [duplicate]

Question

This is probably a very simple question for somebody with experience, but I just wanted to know the safest way to delete a couple of hundred records in an SQL table that fall between a specific range.

For example I need to delete rows with an ID between 79 & 296:

My worry is if I say delete everything with an ID (>79 AND < 296) then it may literally wipe the whole table.

Angiosperm

This is the sort of thing you should be able to test for yourself. If you don't have a play pen where you can try out SQL, especially DML, then you should get one. You should also understand how to recover from a statement going from. For instance, change your environment so it doesn't AUTO COMMIT. That way you can rollback if a statement zaps more records than you intended. – Fridafriday

Could you not just test it first using a select statement? – Toothless

@YvetteColomb surely the answer you have linked is the duplicate, this question was asked nearly two years earlier? – Angiosperm

D.Mac, Q's can be dupes of newer questions (see Meta) and that Q has better SEO if we use votes and views as a proxy. ... Still, @YvetteColomb, I feel it's more appropriate to close the other Q as a dupe of this one. – Grade

Answer 1

If you use Sql Server

delete from Table where id between 79 and 296

Note : the between statement is inclusive, so rows 79 and 296 will also be deleted

After your edit : you now clarified that you want :

ID (>79 AND < 296)

So use this :

delete from Table where id > 79 and id < 296

Mulkey

Or indeed most any SQL database – Fridafriday

Thank you guys I had't come accross between before! still a bit of a novice... thank you for this solution you have saved me about 4 hours of manually deleting records :) – Angiosperm

that works for Sybase as well :) – Tart

Condition in question is ID (>79 AND < 296) this will delete ID (>= 79 AND <= 296), of course it could be not precisely asked question :-). – Saline

FYI: the between statement is inclusive, so rows 79 and 296 will also be deleted – Cadastre

@Cadastre thank you for mentioning this, I feel like the answerer should mention this too – Ehlers

@Ehlers done.......... – Mulkey

@RoyiNamir legend – Ehlers

Answer 2

You gave a condition ID (>79 and < 296) then the answer is:

delete from tab
where id > 79 and id < 296

this is the same as:

delete from tab
where id between 80 and 295

if id is an integer.

All answered:

delete from tab
where id between 79 and 296

this is the same as:

delete from tab
where id => 79 and id <= 296

Mind the difference.

Bouton

Yes, thanks for this... this is what I had in mind to do .My worry was it would first delete everything greater than 79 and then delete evrything less than 296 leaving me with an empty table! Maybe I worry too much :) – Angiosperm

Haha! That would be delete from tab where id => 79 OR id <= 296 - notice the OR instead of AND, which would make it equal just running the two queries seperately. That would indeed delete everything if id is a not nullable integer. If ID can be NULL, those rows remain. – Involve

Answer 3

DELETE FROM table_name 
WHERE id BETWEEN 79 AND 296;

Educatory

Answer 4

you can also just change your delete to a select *

and test your selection

the records selected will be the same as the ones deleted

you can also wrap your statement in a begin / rollback if you are not sure - test the statement then if all is good remove rollback

for example

SELECT * FROM table WHERE id BETWEEN 79 AND 296

will show all the records matching the where if they are the wants you 'really' want to delete then use

DELETE FROM table WHERE id BETWEEN 79 AND 296

You can also create a trigger / which catches deletes and puts them into a history table

so if you delete something by mistake you can always get it back

(keep your history table records no older than say 6 months or whatever business rules say)

Anorexia

Answer 5

If you write it as the following in SQL server then there would be no danger of wiping the database table unless all of the values in that table happen to actually be between those values:

DELETE FROM [dbo].[TableName] WHERE [TableName].[IdField] BETWEEN 79 AND 296 

Broadminded

Answer 6

My worry is if I say delete evertything with an ID (>79 AND < 296) then it may literally wipe the whole table...

That wont happen because you will have a where clause. What happens is that, if you have a statement like delete * from Table1 where id between 70 and 1296 , the first thing that sql query processor will do is to scan the table and look for those records in that range and then apply a delete.

Forge

Answer 7

You can use this way because id can not be sequential in all cases.

SELECT * 
FROM  `ht_news` 
LIMIT 0 , 30

Nagano