CODEWITHSUNDEEP
phpmysql
user2454281
user2454281

Reputation: 73

how to delete duplicate values in mysql table

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

Upvotes: 0

Views: 2183

Answers (2)

Chvanikoff
Chvanikoff

Reputation: 1329

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`
    );

Upvotes: 0

rik
rik

Reputation: 8612

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.

Upvotes: 7

Related Questions