Solved

delete query

Posted on 2013-01-25
15
185 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL export CSV & schedule It 9 44
query help 18 51
optimize  c# code 7 48
Help with SQL field formatting 3 0
'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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

19 Experts available now in Live!

Get 1:1 Help Now