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
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.
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
