I am dealing with a very big database ~ 6 Million records. I've added ~30,000 bad records today. How can I delete all of the records created today in MySQL?
Asked
Active
Viewed 1.4k times
7
-
Do they have creation timestamps? – icktoofay Mar 25 '12 at 22:41
-
yes created_at: "2012-03-25 21:52:21" but each is very specific – JZ. Mar 25 '12 at 22:42
-
1Please, don't tell that you don't store data's creation time in your table.? – Korhan Ozturk Mar 25 '12 at 22:43
-
What data type are they stored as? – icktoofay Mar 25 '12 at 22:43
-
datetime via ruby on rails active record... I don't know what that translates to in mysql – JZ. Mar 25 '12 at 22:45
4 Answers
21
It seems created_at is a datetime. Try:
delete from table
where date(created_at) = curdate()
Of course, run a select * prior to run this query and make sure the data you're going to delete is the one you really want to delete.
Mosty Mostacho
- 42,742
- 16
- 96
- 123
1
The condition
WHERE created_at >= '2012-03-25'
AND created_at < '2012-03-26'
could be used to identify the rows (and quite efficiently if there is an index on created_at).
Before deleting, make sure you backup the table (or even better, the whole database). Additionally, you can use some (temp or permament) table to have the rows stored, before deleting them from your table. Then, you delete this temp table when you are sure you have erased the offending data - and nothing else:
CREATE TABLE wrong_data AS
SELECT *
FROM tableX
WHERE created_at >= '2012-03-25'
AND created_at < '2012-03-26' ;
DELETE t
FROM tableX AS t
JOIN wrong_data AS w
ON w.PK = t.PK ;
ypercubeᵀᴹ
- 113,259
- 19
- 174
- 235
0
DELETE FROM Table WHERE ( (DAY(CallDate) = DAY(GETDATE()) AND (MONTH(CallDate) = MONTH(GETDATE()) AND (YEAR(CallDate) = YEAR(GETDATE()) )
helmetwearer
- 104
- 5
0
Try below:
delete from table
where left(created_at,10) =curdate()