-1

I get database table which contain postal numbers and regions for my country. That table have all information but i need to change it for my purpose.

I need to eliminate all rows that have duplicate content in specific column.

Check screenshot to see result enter image description here

I want to remove all duplicate rows which have postanski_broj (postal_number) the some. That number need to be unique. I try manualy to set that column to unique but i get duplicate entry when i try to execute statment.

  • ID is primary key with auto increment.
  • postanski_broj column is VARCHAR which represent postal_code
  • naselje column is VARCHAR which represent region

One region can have one postal_code

I try

ALTER  TABLE poste ADD UNIQUE INDEX idx_postanski_br (postanski_broj);

00:03:20 ALTER TABLE poste ADD UNIQUE INDEX idx_postanski_br (postanski_broj) Error Code: 1062. Duplicate entry '11158' for key 'idx_postanski_br' 0.118 sec

ALTER IGNORE TABLE poste ADD UNIQUE INDEX idx_postanski_br (postanski_broj);

00:04:17 ALTER IGNORE TABLE poste ADD UNIQUE INDEX idx_postanski_br (postanski_broj) Error Code: 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 'IGNORE TABLE poste ADD UNIQUE INDEX idx_postanski_br (postanski_broj)' at line 1 0.00037 sec

Anyone have sugestion? Thanks

Ivan
  • 5,139
  • 11
  • 53
  • 86
  • Please provide your data and code as text rather than images. – GMB Nov 01 '22 at 23:10
  • Thanks for vote down! I put image like result output and example code what i try as text. I cant put visual result as text. – Ivan Nov 01 '22 at 23:14
  • Why are you trying `ALTER` instead of `DELETE`? I mean, you are trying to ALTER your table to set a new constraint (unique), I got it, but that must be done after deleting the rows. – Jaky Ruby Nov 01 '22 at 23:16
  • My plain is to add uniqe and i thinked that unique will eliminate duplicate. Let me try with delete – Ivan Nov 01 '22 at 23:17

2 Answers2

1

This solution can take too much time for big tables. Best way of solving this is: Remove duplicate rows in MySQL

You have to delete the rows before applying the unique constraint. Be careful applying this:

DELETE p1 FROM poste p1
INNER JOIN poste p2 
WHERE 
    p1.id < p2.id AND 
    p1.postanski_broj = p2.postanski_broj;

This should remove the duplicated ones and will keep only the ones with the higher id (id=168044 in your example).

Jaky Ruby
  • 1,409
  • 1
  • 7
  • 12
1

If you have other columns with different values than the ones you've shown there (except for id), deleting should be your last choice.

I usually would duplicate the table first:

CREATE TABLE poste_new LIKE poste;

add unique index to the newly created poste_new table:

ALTER  TABLE poste_new ADD UNIQUE INDEX idx_postanski_br (postanski_broj);

insert the data from poste into poste_new with IGNORE option to skip duplicates based on the unique index:

INSERT IGNORE INTO poste_new SELECT * FROM poste;

rename the tables:

RENAME TABLE poste TO poste_old;
RENAME TABLE poste_new TO poste;

The good thing about this is that you've minimized the risk of wrong delete and if you're not satisfied with the new table, you still have the old table intact - effectively making it a backup.

FanoFN
  • 6,815
  • 2
  • 13
  • 33
  • I cant use IGNORE i put in question Mysql Workbench dont allow. – Ivan Nov 01 '22 at 23:57
  • That's `ALTER IGNORE`.. the one I suggest is `INSERT IGNORE` – FanoFN Nov 01 '22 at 23:58
  • let me test this code – Ivan Nov 02 '22 at 00:04
  • 1484 row(s) affected, 64 warning(s): 1062 Duplicate entry '11158' for key 'idx_postanski_br' 1062 Duplicate entry '11158' ..... I do first like you say to create after update and then when i try to insert i get this – Ivan Nov 02 '22 at 00:18
  • Yes, now you can check the new table and see if there are duplicates. – FanoFN Nov 02 '22 at 00:20
  • 1
    I can tell you thanks for time this is good way to not lost data and fix problem. Now work. Thanks again – Ivan Nov 02 '22 at 00:31