用一个独特的值更新MySQL字段。

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.

我正在寻找一个mysql更新查询，它将把两个字段连接在一起，然后在这里仍然存在唯一的值，为每个值添加一个增量，并更新第三个字段。如果连接的值是惟一的，它还应该为连接添加1。

Example

例子

Field1   Field2   UniqueValue
======   ======   ===========
A        B        AB1
A        A        AA1
A        A        AA2
A        A        AA3
C        D        CD1

1 个解决方案

#1

You can use user variable to generate incrementing number per unique combination of the fields and then concatenate them together.

您可以使用user变量来生成每个字段的惟一组合的递增数，然后将它们连接在一起。

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;

Demo

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:

如果您的表中有一个id列，那么您可以使用上面的查询来连接您的表，以执行更新:

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;

Demo

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:

如果您没有id列，那么解决这个问题的一种方法是使用新表加载新值，然后将其重命名为原始表:

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;

Demo