SQL Delete entries within a specific range

Question

This is probably a very simple question for someone who has experience, but I just wanted to find out how a safe way to delete several hundred records in an SQL table that fall between a certain range.

For example, I need to delete lines with an identifier between 79 and 296:

My concern is that I can delete everything with an identifier (> 79 AND <296), after which it can literally erase the entire table.

Answer 1

if you use sql server

delete from Table where id between 79 and 296 

After your editing: now you have specified what you want:

ID (>79 AND < 296)

So use this:

delete from Table where id > 79 and id < 296

Answer 2

You gave a status identifier (> 79 and <296), then the answer will be as follows:

 delete from tab where id > 79 and id < 296 

it 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 

it is the same as:

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

Pay attention to the difference.

Answer 3

 DELETE FROM table WHERE id BETWEEN 79 AND 296 

Answer 4

If you write it like on a SQL server, then there will be no danger of wiping the database table if all the values ​​in this table are actually not between these values:

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

Answer 5

My concern is that I say delete evertything with identifier (> 79 AND <296) then it can literally erase the whole table ...

This will not happen because you will have a where clause. It happens that if you have an expression like delete * from Table1 where id between 70 and 1296 , the first thing the sql query processor will do is scan the table and look for these records in this range, and then apply deletion.

Answer 6

you can also just change your deletion to select *

and check your choice

the selected entries will be the same as those that were deleted

you can also wrap your statement at the beginning / rollback, if you are not sure - check the statement, if everything is good remove the rollback

eg

SELECT * FROM table WHERE ID BETWEEN 79 AND 296

will display all entries matching if this is what you really want to delete use

DELETE FROM table WHERE ID BETWEEN 79 AND 296

You can also create a trigger / that catches deletes and puts them in the history table

therefore, if you delete something by mistake, you can always return it

(keep entries in the history table no older than 6 months or, oddly enough, in the business rules)

Answer 7

You can use this method because the identifier cannot be consistent in all cases.

 SELECT * FROM `ht_news` LIMIT 0 , 30