I have column in mysql table (my_sale_time) of type timestamp....infact the rows value look like this
2010-12-01 14:38:07
2010-12-01 17:14:18
...
so what i need mysql query to delete those value whose date is repeated multiple times in table.....as i mentioned in sample sale_time_value.....only date is repeated with same value but time is different....so i want to get all rows, date is repeated multiple times and delete duplicate dates
The basic principle of deleting duplicate rows:
CREATE TEMPORARY TABLE tmptbl AS SELECT DISTINCT * FROM my_sale_time;
DELETE FROM my_sale_time;
INSERT INTO my_sale_time SELECT * FROM tmptbl;
You may have to specify columns and WHERE clauses (I didn't really understand your criteria). And of course you should test-run it on a development server and don't forget to run it as a single transaction with locked tables.
CREATE TEMPORARY TABLE tmptbl AS SELECT DISTINCT DATE(sale_time), * FROM my_sale_time and check if tmptbl contains the rows you want.
If you have an auto_increment field, use this:
DELETE FROM
`mytable`
WHERE
`my_auto_increment_field` NOT IN (
SELECT
MAX(`my_auto_increment_field`)
GROUP BY
`my_sale_time`
);
