I am looking for a mysql update query that will concatenate two fields together and then where unique values still exist add a increment to each value and update a third field. If the concatenated value is unique it should still have to add 1 to the concatenation.
Example
Field1 Field2 UniqueValue
====== ====== ===========
A B AB1
A A AA1
A A AA2
A A AA3
C D CD1
You can use user variable to generate incrementing number per unique combination of the fields and then concatenate them together.
select field1, field2, concat(field, rn) UniqueValue
from (
select
t.*,
@rn := if(@field = field, @rn + 1, if(@field := field, 1, 1)) rn
from
(select
field1, field2,
concat(field1, field2) as field
from your_table
order by field
) t, (select @rn := 0, @field := null) t2
) t;
If you want to update the table with the generated uniqueValue -
If you have an id column in your table, you could join your table with the above query on that id to do the updates:
update your_table t1 join (
select id, concat(field, rn) UniqueValue
from (
select
t.id,
field,
@rn := if(@field = field, @rn + 1, if(@field := field, 1, 1)) rn
from
(select
id, field1, field2,
concat(field1, field2) as field
from your_table
order by field
) t, (select @rn := 0, @field := null) t2
) t
) t2 on t1.id = t2.id
set t1.uniqueValue = t2.UniqueValue;
If you don't have an id column, then one way to solve this is using a new table to load the new values and then rename it to the original table:
drop table if exists your_table;
drop table if exists your_table_new;
drop table if exists your_table_old;
CREATE TABLE your_table(
Field1 VARCHAR(10) NOT NULL
,Field2 VARCHAR(10)
,UniqueValue Varchar(20)
);
INSERT INTO your_table(Field1,Field2) VALUES ('A','B');
INSERT INTO your_table(Field1,Field2) VALUES ('A','A');
INSERT INTO your_table(Field1,Field2) VALUES ('A','A');
INSERT INTO your_table(Field1,Field2) VALUES ('A','A');
INSERT INTO your_table(Field1,Field2) VALUES ('C','D');
create table your_table_new (field1 varchar(10), field2 varchar(10), uniquevalue varchar(20));
insert into your_table_new (field1, field2, uniqueValue)
select field1, field2, concat(field, rn) UniqueValue
from (
select
t.*,
@rn := if(@field = field, @rn + 1, if(@field := field, 1, 1)) rn
from
(select
field1, field2,
concat(field1, field2) as field
from your_table
order by field
) t, (select @rn := 0, @field := null) t2
) t;
rename table your_table to your_table_old, your_table_new to your_table;
@field to zero but not null. It should be @field := 0
