0

I have a mysql table I want to remove the duplicate with a certain condition

I want to remove if name, cl_1, cl_2, cl_3, cl_4, cl_5, cl_6, are exactly same other wise if one was different and other columns were same no need to remove.

for example I want to remove row 1 and 4 not 7

id  name    cl_1    cl_2    cl_3    cl_4    cl_5    cl_6    
--------------------------------------------------------------  
1   name1   T1      T2      T3      T4      T5      T6    (<< Remove)
2   name2   L1      M2      L3      L4      T5      T6
3   name3   T1      T2      T3      T4      T5      T6
4   name1   T1      T2      T3      T4      T5      T6  (<< Remove)
5   name2   T1      T2      T3      T4      T5      T6
6   name3   T1      T2      T3      T4      T5      T6
7   name1   T1      T2      T3      T4      T5      K6  (Not this one)
8   name5   T1      T2      T3      T4      T5      N6
9   name6   T1      T2      T3      T4      T5      H6
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
amirali shahinpour
  • 189
  • 2
  • 13
  • 1
    So what's the problem? What have you tried? – Ja͢ck Mar 09 '13 at 02:53
  • I don't wanna be that guy, but that database structure looks like it REALLY needs to be normalized... – Andrew McGivery Mar 09 '13 at 03:00
  • it is not the real data, it's just a sample to show you guys what I want, I know how to remove duplicate but it depends on duplication in one columns. So what I want is to check more than one col and if all were same then remove it. – amirali shahinpour Mar 09 '13 at 03:04
  • 1
    Can you explain more precisely which rows you want to remove? It's not clear from your example – Jodes Mar 09 '13 at 03:06
  • You want to remove __both__ rows that are duplicates, or you want to remove all those after the first instance? – Jeff Rosenberg Mar 09 '13 at 03:07

1 Answers1

2

This should work:

DELETE Y
FROM YourTable Y JOIN 
   (
  SELECT
    name, cl_1, cl_2, cl_3, cl_4, cl_5, cl_6 
  FROM YourTable
  GROUP BY name, cl_1, cl_2, cl_3, cl_4, cl_5, cl_6
  HAVING COUNT(1) > 1
     ) T ON Y.name = T.name 
        AND Y.cl_1 = T.cl_1
        AND Y.cl_2 = T.cl_2
        AND Y.cl_3 = T.cl_3
        AND Y.cl_4 = T.cl_4
        AND Y.cl_5 = T.cl_5
        AND Y.cl_6 = T.cl_6

SQL Fiddle Demo

BTW -- What about rows 3 and 6? Those seem to be the exact same?

http://sqlfiddle.com/#!2/26b8b/1

sgeddes
  • 62,311
  • 6
  • 61
  • 83