delete duplicate

[BubikolRamios]

data, if this is 3 col table

8 2 9
5 2 7

this is at my problem duplicate record (column 2), how to find/remove one of records, does not matter which.

?

[Old Pedant]

Hmmm...

c1 c2 c3
8 2 9
5 2 7

Maybe this:


DELETE t1
FROM table AS t1,
(SELECT MAX(c1) AS c1max, c2 FROM table GROUP BY c2) AS t2
WHERE t1.c1 <> t2.c1max AND t1.c2 = t2.c2 ;

Now, that doesn't work if you have data like:

c1 c2 c3
8 2 9
8 2 7

So then you follow that with another query:

DELETE t1
FROM table AS t1,
(SELECT MAX(c3) AS c3max, c1, c2 FROM table GROUP BY c1, c2) AS t2
WHERE t1.c3<> t2.c3max AND t1.c1 = t2.c2 AND t1.c2 = t2.c2 ;

COMPLETELY UNTESTED!

Use at your own risk! (Back up your table first.)

[BubikolRamios]

does not work, does not find 'duplicates'

This finds last duplicate of all duplicates , assuming that c2 is column with repetitive data,
data not containing '|' and
c1,c2,c3,... is list of all columns in table.

SELECT CONCAT(t1.c1,t1.c2,t1.c3) as c
FROM table t1
left join (Select distinct(c2) from table ) as t2
on t1.c2 = t2.c2
group by t1.c2
having LOCATE('|',GROUP_CONCAT(t2.c2 SEPARATOR '|')) > 0 ;


--> so if jou have rows like

c2

apple
apple
apple
pear


--> the query would return last apple row.

However, deleting data based on this is mistake, you have first to remove ordinary duplicates (data same in all columns)

Delete allso does not work direct, have to write script with temp table involved, at least I don't know how to do it direct.