Solved

delete query

Posted on 2013-01-25
15
186 Views
Last Modified: 2013-02-11
I have a huge table with a created_date, I want to delete all the records more than 45 day. how the query works?

here is what I write

Select * from mytable where created_date>dateadd(day,+45,getdate())
0
Comment
Question by:wasabi3689
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820684
If that shows you the records you want to delete then
Delete from mytable where created_date>dateadd(day,+45,getdate())
will delete those same records
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38820690
What you're doing there is adding 45 days to today's date.

If you want to see everything older than 45 days ago that would be deleted you'd want:

Select * from mytable where created_date < dateadd(dd,-45,getdate())

Dateadd documentation is here: http://msdn.microsoft.com/en-us/library/ms186819.aspx

(There is no "day" operator for dateadd according to the documentation)
0
 
LVL 39

Expert Comment

by:lcohan
ID: 38820698
Hm.....do you have data in the future that you want to delete?

Do you have index on that column?

Is that an ONLINE table accessed (INSERT/UPDATE/DELETE) often?


I would rather schedule a job and do it in batches - something like Large batch(put all row IDentifiers in a table that meets your criteria and needs to be deleted) then use a small batch table to delete batches of xxxxxx rows from that.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38820700
Try this:

DELETE FROM mytable
WHERE created_date < getdate() - 45
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820702
yeah, I responded too fast and did not read carefully enough so it should have been
Delete from mytable where created_date < dateadd(day,-45,getdate()) 

Open in new window

But, there is a DAY datepart that works in dateadd.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 38820706
I strongly suggest do a

Select count(*) from mytable where created_date < getdate()-45 before that to see how many rows you have to delete...
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820710
getdate() - 45
@mbizup, careful, the subtraction opperator in Date types is being depricated in SQL Server, I do not recommend that practice to others anymore as it will not always work.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:wasabi3689
ID: 38820718
I want to remove all records older than 45 days
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820732
are you meaning you want to do that with a manual query now, or that you want something that will keep removing records older than 45 days?  If so then you need to put one of these delete queries in a JOB scheduled to run every day.
0
 

Author Comment

by:wasabi3689
ID: 38820759
I want something that will keep removing records older than 45 days
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820773
http://msdn.microsoft.com/en-us/library/ms190268.aspx
Here is the MS page for creating a SQL Server JOB
0
 

Author Comment

by:wasabi3689
ID: 38820791
again,

where is better??? why


DELETE FROM mytable
WHERE created_date < getdate() - 45

or

Delete from mytable where created_date < dateadd(day,-45,getdate())


I have a huge table. I need fast delete
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820804
Either is going to work and the performance will not be different.  Delete operations of lots of records are going to be a slow operation, expecially if you have many indexes on the table.  If you can stand the down time, it might be better to delete the indexes first, then delete the rows, then reapply the indexes.  That would speed up the delete and your index stats would be correct when you rebuilt it.
0
 

Author Comment

by:wasabi3689
ID: 38820819
someone recommends the following

Following is the TSQL code to perform batch deletions:

Use AdventureWorks
Go

–Create a copy of Sales.SalesOrderHeader as a test table
Select *
into testOrderHeaderTable
from Sales.SalesOrderHeader

–Delete 5000 Rows at a time
While 1=1
Begin
Delete Top(5000) testOrderHeaderTable

—Check for exit condition
If @@rowcount <5000 break
End

–Remove the test table
Drop table testOrderHeaderTable


What do you think?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38821646
What they are doing is breaking the deletes up into batches, because like I said deletes can be an expensive operation and puts locks the table keeping other queries from being able to use it while it is being performed.
This example is not exactly what you need because it is not date specific only raw count driven but the concept is fine.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now