MySql update with different values a column for duplicate columns

Question

I have a table like this

CREATE TABLE IF NOT EXISTS users_groups(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT(11) UNSIGNED NOT NULL,
  name VARCHAR(250) NOT NULL,
  is_deleted BOOLEAN NOT NULL DEFAULT false,
  creation_date DATETIME NOT NULL,
  last_modification_date DATETIME NOT NULL,
  PRIMARY KEY (id)
);

And I would like to turn the columns (user_id, name) as unique, but the problem I have is that I already have duplicates in that columns. In some cases the columns are repeated many times.

I would like to write a query to update the duplicates rows concatenating some value in the name, for example concatening '*' for the first duplicate '**' for the second duplicate, and so on.

Is this possible writing a query?

I can get the group that I want to modify, but I am not sure if it is possible to achieve what I want with mysql

select users_groups.id, users_groups.user_id, users_groups.name
from users_groups
inner join (select user_id, name, count(*) from users_groups group by user_id, name having count(*) > 1) as u on u.user_id = users_groups.user_id
where users_groups.name = u.name;

ydoow · Accepted Answer · 2017-03-08 00:08:18Z

1

Try this.

SET @prev_user_id := '';
SET @prev_name := '';
SET @cnt := 0;

select s.id, s.user_id, s.name
from 
(
  select 
    users_groups.id,     
    IF(@prev_user_id <> users_groups.user_id OR @prev_name <> users_groups.name, @cnt := 0, @cnt := @cnt + 1) AS cnt,   
    CONCAT(users_groups.user_id, REPEAT('*', @cnt)) AS user_id,
    CONCAT(users_groups.name, REPEAT('*', @cnt)) AS name,  
    @prev_user_id := users_groups.user_id,
    @prev_name := users_groups.name
  from 
    users_groups
) s

Check here: http://sqlfiddle.com/#!9/6d553/10

edited Mar 8, 2017 at 0:08

answered Mar 6, 2017 at 23:49

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

agusgambina Over a year ago

thank you for your answer. It did not work, I am not sure why, but it does not recognize the variable @cnt in the select statement.

ydoow Over a year ago

@agusgambina sorry there's wrong text used, try again.

agusgambina Over a year ago

thank you it worked, I only need to add the update statement between the set and the select and add the where clause for the id at the end.

Chris · Accepted Answer · 2017-03-06 22:38:50Z

0

I'm not sure if this helps, but you can make a column unique without it having to be the primary key of that table.

For example:

CREATE TABLE user (
    user_id NOT NULL,
    firstname varchar(255) NOT NULL,
    UNIQUE (firstname)
)

Or the following statement which creates a unique constraint across multiple columns.

CREATE TABLE user (
    user_id NOT NULL,
    firstname varchar(255) NOT NULL,
    CONSTRAINT constraint_name UNIQUE (user_id, firstname)
)

Also, I'm not entirely sure why you would want to identify duplicates. It's good practice to avoid duplicate records and data entries entirely. This can be done through table normalization, as well as the use of appropriate indexing.

I hope this helps in some way.

answered Mar 6, 2017 at 22:38

Chris

8071 gold badge12 silver badges28 bronze badges

2 Comments

Chris Over a year ago

I may have misunderstood your question. However, it's good practice to avoid duplicates entirely. If you make use of proper indexing and keys then you will never have a completely duplicate row. You may well have columns in a row that are duplicate to other rows, but you wouldn't use these rows to identify one single record.

agusgambina Over a year ago

@Chris_Mulheron thank you for your answer. I totally agree with you, but it is something that already happened and I have to fix it.

Collectives™ on Stack Overflow

MySql update with different values a column for duplicate columns

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related