3

I have a CDR Table with a "unique id" column which is not a Primary Key. During an upgrade, there were a few days where instead of one entry for a caller ringing a queue, there was multiple entries for this caller. All the entries have the same unique ID.

What I would like to do, is delete all entries with the same unique ID, but leaving one entry. Is there a simple way to do this? All my searching turned up results on either, DB's with no unique ID, or with a Primary key. I have both a unique ID and No Primary key lol.

Thanks for your guidance!

PS: MySQL 5.5

Eg: enter image description here

  • When you say, unique id, do you mean it is supposed to be unique, but it has same value repeated? If it is a UNIQUE KEY, no duplication is allowed. Data sample would be helpful – Jehad Keriaki Feb 17 '17 at 18:26
  • @JehadKeriaki Yes the row name is "unique ID", but there are duplicate Unique ID's. It is not a Primary Key, so its not MySQL's fault that it allowed multiple entries with the same Unique ID – FreeSoftwareServers Feb 17 '17 at 19:34
  • That is one ring in the queue... so you image, that table grew a lot those 2 days! Thankfully, we can export to excel and de-duplicate, but id like to fix the data in the DB – FreeSoftwareServers Feb 17 '17 at 19:40
1

If more than, say, half the table needs to be removed, it is faster (etc) to

CREATE TABLE new LIKE real;
ADD PRIMARY KEY (...);   -- or UNIQUE
INSERT INTO new
    SELECT DISTINCT ... FROM real;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;
0

Couple of ways to do that [DO MAKE A BACKUP BEFORE TRYING]:

1- Using ALTER IGNORE:

ALTER IGNORE my_tablee ADD UNIQUE (CallerID);

2- Create a new table, with the same structure, and preferably add the unique key in order not to fall into the same problem in the future:

CREATE TABLE new_table LIKE my_table;
INSERT INTO new_table SELECT * FROM my_table GROUP BY CallerID;
ALTER TABLE my_table ADD UNIQUE (CallerID); --Optional
  • But I do have multiple unique entries with the same caller ID will this remove those? I'll test next week thanks u – FreeSoftwareServers Feb 17 '17 at 23:19
  • In the provided queries, replace CallerID with the name of the unique id field. – Jehad Keriaki Feb 19 '17 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.