?
Solved

delete query

Posted on 2013-01-25
15
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 27

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 40

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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 61

Accepted Solution

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

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

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 40

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 27

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
 

Author Comment

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

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 27

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 27

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 27

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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