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.
This question is related to
sql
sql-delete
records
DELETE FROM table_name
WHERE id BETWEEN 79 AND 296;
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.
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.
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
You can use this way because id can not be sequential in all cases.
SELECT *
FROM `ht_news`
LIMIT 0 , 30
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)
Source: Stackoverflow.com