Delete n number of rows at a time

Hi all,

I need to delete 1000 records at a time starting from now-1 day,
and I need it for both DB2 and SQLServer.

If it's 13:50 3/11/2014,
I need to delete 1000 records at a time starting from 13:50 3/10/2014

Is there a sql statement that I can (repeatedly) construct and then call repeatedly  until it is done?

Thank you,
trustworthyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
momi_sabagConnect With a Mentor Commented:
could you post here the structure of your tables?
basically you should be able to do it using sub-queries
something like

for db2
delete from table
where primary_key in (select primary key from your_table where... fretch first 1000 rows only)

for sql
delete from table
where primary_key in (select top 1000 primary key from your_table where... )
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in sql server, you have 2 options to delete 1000 rows max:
SET ROWCOUNT 1000
DELETE yourtable WHERE ....

Open in new window

 DELETE TOP 1000 yourtable WHERE ... 

Open in new window


>if it's 13:50 3/11/2014, I need to delete 1000 records at a time starting from 13:50 3/10/2014

I presume that's related to the field in the column, hence the WHERE condition.
so, you shall do something like this:
WHERE yourfield < dateadd(day, -1, getdate())

>that I can (repeatedly) constru
deleting 1000 rows at a time, repeatetly will eventually use MORE resources than doing the job "at once", though there are exceptions, especially if the table to delete from has high number of lockings ... having a clustered index on the date/time field will help there a lot.

apart from that, you can do this, taking example 1 above:
declare @r int
set @r = 1
SET ROWCOUNT 1000
WHILE @r <> 0
BEGIN
   DELETE yourtable WHERE your_date_field < dateadd(day, -1, getdate())
   set @r = @@ROWCOUNT
END 

Open in new window


I presume that db2 is very similar in this regards
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
All of a3's above solutions will work.  

If your table / total number of rows you're deleting is huge, another benefit of the WHILE loop solution is that you can also insert rows into a 'log' table, if you feel the need to want to know the status of this code while it is executing.
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Use Momi_Sabag's suggestion.  :)

You can also use that technique when the timestamp column is not the primary key, but it is indexed.  (If the column isn't indexed and you want to delete a specific 1,000 rows all techniques could have performance issues.)

delete from table
where primary_key in (select primary key from your_table where... fetch first 1000 rows only)

or

delete from table
where date_column in (select date_column from your_table where... fetch first 1000 rows only)




Hi Momi.  Haven't seen you around in a bit.  I hope that all is well with you!
0
 
trustworthyAuthor Commented:
Thank you all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.