SQL Delete Records within a specific Range

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  amp  296   My worry is if I say delete everything with an ID   79 AND  lt  296  then it may literally wipe the whole table

User · Answer

You can use this way because id can not be sequential in all cases   SELECT    FROM   ht news   LIMIT 0   30

User · Answer

My worry is if I say delete evertything with an ID   79 AND  lt  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

User · Answer

You gave a condition ID   79 and  lt  296  then the answer is   delete from tab where id  gt  79 and id  lt  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   gt  79 and id  lt   296   Mind the difference

User · Answer

DELETE FROM table name  WHERE id BETWEEN 79 AND 296

User · Answer

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

User · Answer

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

User · Answer

if you use Sql Server   delete from Table where id between 79 and 296   After your edit   you now clarified that you want     ID   gt 79 AND  lt  296   So use this     delete from Table where id  gt  79 and id  lt  296

[sql] SQL Delete Records within a specific Range

Examples related to sql

Examples related to sql-delete

Examples related to records