How to delete redundant records with different dates from a MySQL table
I have a table in a MySQL database that consists of the following columns:
itemID bigint(11)
itemDate datetime
attributeID smallint(6)
value int(9)
What would be the SQL queries (?) to best delete (starting from most
recent records to the oldest):
each record in this table that has value = 0 if exists (another record
with the same itemID and same attributeID and that has a value >2 and the
itemDate is (older but also most recent) OR identical)
each record in this table if exists (another record with the same itemID
and same attributeID and same value and the itemDate is (older but also
most recent) OR identical)
I am using this in a PHP script.
Basically, I have redundant data due to a bug that I had not identified
soon enough for it not to populate around 100k entries. A very small
example below:
itemID itemDate attributeID value
28 11.09.2013 2:00 4 0
28 11.09.2013 2:00 5 0
28 11.09.2013 2:01 1 0
28 11.09.2013 2:01 2 0
28 11.09.2013 2:01 3 0
28 11.09.2013 2:01 4 0
28 11.09.2013 2:01 5 0
28 11.09.2013 2:02 1 21
28 11.09.2013 2:02 2 11
28 11.09.2013 2:02 3 4
28 11.09.2013 2:02 1 21
28 11.09.2013 2:02 2 11
28 11.09.2013 2:02 3 4
28 11.09.2013 2:02 1 21
28 11.09.2013 2:02 2 12
28 11.09.2013 2:02 3 4
28 13.09.2013 18:54 1 0
28 13.09.2013 18:54 2 0
28 13.09.2013 18:54 3 0
28 13.09.2013 18:55 1 21
28 13.09.2013 18:55 2 12
28 13.09.2013 18:55 3 6
The above should become (after multiple iterations of the deletion
algorythm):
itemID itemDate attributeID value
28 11.09.2013 2:00 4 0
28 11.09.2013 2:00 5 0
28 11.09.2013 2:01 1 0
28 11.09.2013 2:01 2 0
28 11.09.2013 2:01 3 0
28 11.09.2013 2:02 1 21
28 11.09.2013 2:02 2 11
28 11.09.2013 2:02 3 4
28 11.09.2013 2:02 2 12
28 13.09.2013 18:55 3 6
I hope I defined the problem clear enough, however, please let me know if
I should clarify anything. Thank you kindly!
No comments:
Post a Comment