Removing Duplication using MySQL Update statment

Question

How to use counter in UPDATE statement. For example, I have some duplicate records in a column as follows:

--------------------
      Ref_Number
--------------------
108001798914
108001798914
108001798914
108001798914
108001798914
108001798914

How to remove the duplication so the result should be

--------------------
      Ref_Number
--------------------
108001798914
108001798915
108001798916
108001798917
108001798918
108001798919

possible duplicate of [MySQL remove duplicates from big database quick](http://stackoverflow.com/questions/1651999/mysql-remove-duplicates-from-big-database-quick) — Oyeme, Feb 18 '14 at 15:24

Gordon Linoff · Answer 1 · 2014-02-18T16:05:18.000

The following will get the information needed for a new ref_number -- assuming there are no conflicts with existing ones:

select ref_number,
       @rn := if(@refnum = ref_number, @rn + 1, 0) as seqnum,
       @refnum = rev_number
from table t cross join
     (select @rn := 0, @refnum := -1) const
order by ref_number;

You can put this into an update using join, assuming you have an id column:

update table toupdate join
             (select @rn := 0, @refnum := -1, @prev_refnum := -1) const
     set ref_number = ref_number +
                      (case when (@prev_refnum := @refnum) is null then NULL
                            when (@refnum := ref_number) is null then NULL
                            when ref_number := @prev_refnum then @rn := @rn + 1
                            else 0
                       end)
     order by ref_number;

This is a rather complicated statement because MySQL does not make it easy to set variables in an update statement. The use of the case is simply to set the variables to remember the previous value. They are executed in order, even though they fail.

Volkan Ulukut · Accepted Answer · 2014-02-18T16:19:28.087

1

try this. don't forget to make a backup of your data before running these:

CREATE TABLE temp_table LIKE my_table;

ALTER TABLE temp_table ADD UNIQUE (Ref_Number);
ALTER TABLE temp_table CHANGE Ref_Number Ref_Number INT(10)AUTO_INCREMENT PRIMARY KEY;

INSERT INTO temp_table (all,other,fields,except,refnumber,here)
SELECT all,other,fields,except,refnumber,here FROM my_table

TRUNCATE my_table;

ALTER TABLE my_table ADD UNIQUE (Ref_Number);
ALTER TABLE my_table CHANGE Ref_Number Ref_Number INT(10)AUTO_INCREMENT PRIMARY KEY;

INSERT INTO my_table 
SELECT * FROM temp_table

DROP temp_table;

this is the sample sqlfiddle

edited Feb 18 '14 at 16:19

answered Feb 18 '14 at 15:23

Volkan Ulukut

4,230
1
20
38

Sorry, I could not explain my question. I have many columns in the table and I want the duplicate records to be updated with unique ones. like using MAX(Ref_Number) and adding one to it and then updating and so on. – user1369905 Feb 18 '14 at 15:25
I mean that with unique numbers. 108001798914 108001798914 108001798914 108001798914 108001798914 108001798914 with 108001798914 108001798915 108001798916 108001798917 108001798918 108001798919 – user1369905 Feb 18 '14 at 15:27
you want them to be incremental? – Volkan Ulukut Feb 18 '14 at 15:28
Yes that's what I want. Incrementing and updating – user1369905 Feb 18 '14 at 15:29
Looks like it's close to what I'm looking for. Thanks. I now give it a go. – user1369905 Feb 18 '14 at 15:36
I don't believe this works. Here is a simple SQL Fiddle that doesn't work as expected: http://www.sqlfiddle.com/#!2/4c5d4c. The most obvious reason, though, is that there is no unique constraint on `ref_number`. – Gordon Linoff Feb 18 '14 at 15:55
oh right. you need to alter table and give ref_number unique attribute after you truncated my_table. just added relevant query. – Volkan Ulukut Feb 18 '14 at 15:56
okay now, there were some problems in my version. but i've fixed them all and added sqlfiddle to test it. thanks @GordonLinoff for pointing it out. – Volkan Ulukut Feb 18 '14 at 16:20

Removing Duplication using MySQL Update statment

2 Answers2