-3

i have the following table. I need to remove duplicate rows based on the zip column.

id      state       zip
1       CA          112233
2       CA          112233
3       CA          112233
4       CA          113300
.       .           .
.       .           .   
999     FL          345678
1000    FL          234579

thanks!

john
  • 133
  • 1
  • 2
  • 8

2 Answers2

2

The easier way is to identify the ones you need to keep.... try this

DELETE FROM  `you-table`
WHERE
   id not in (
               select *
                  from(
                       SELECT
                          min(t.id) as keepID
                       from
                           `you-table` as t
                       group by concat(t.state,t.zip)
                      ) as keepTable
              )
Shujaat
  • 691
  • 4
  • 18
1

If the number of repetitions is low you can repeat this command the time you need otherwise you shuold use an iteration 

delete from yourTable AS a  where a.id in (select max(b.id) from yourTable AS b 
 group by b.zip having count(b.id) >1 );

Caution make a copy before try.

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • #1093 - You can't specify target table 'yourTable ' for update in FROM clause – john Nov 07 '15 at 21:51
  • I have update the answer – ScaisEdge Nov 07 '15 at 21:55
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS a where a.id in (select max(b.id) from MyTable AS b group by b.zip ha' at line 1 – john Nov 07 '15 at 22:22