CODEWITHSUNDEEP
mysqlsqlduplicates
Jose Silva
Jose Silva

Reputation: 13

Sql renaming several raw data

I have been through some solutions published here, but none of them solved my problem.

I want to set my username column to receive now unique usernames.

But for that I need to rename in certain situations, more than 1000 duplicated usernames already registered.

I tried this solution:

UPDATE profile n
JOIN (SELECT username, MIN(profile_id) min_id FROM profile
      GROUP BY username HAVING COUNT(*) > 1) d
ON n.username = d.username AND n.profile_id <> d.min_id SET n.username = CONCAT(n.username, '1');

But it gives me for the same user name for example tony, tony1, tony11, tony111 and so on up to tony1111111111111... up to 1000, make the username have a long long lenght.

I would like a solution to get only up 4digites after the username word. 0001,0002,0003,0004....1000

Can somebody help me here?

Thank you in advance

Upvotes: 0

Views: 50

Answers (2)

Argod
Argod

Reputation: 161

how about something like:

UPDATE profile n JOIN (
    SELECT profile_id, username, (@row_number:=@row_number+1) as cntr 
    FROM profile,  (SELECT @row_number:=0) AS t 
    WHERE username IN ( SELECT username 
                FROM profile 
                GROUP BY username HAVING COUNT(*) > 1 )
            and (username, profile_id) not in ( SELECT username, MIN(profile_id)
                FROM profile 
                GROUP BY username HAVING COUNT(*) > 1 )
    ) d ON n.profile_id = d.profile_id 
SET n.username = CONCAT(n.username, d.cntr);

This is the best I can come up with at the moment.... the problem is that it will share the counter between all usernames... you you will have Alejandro, Alejandro1, Pedro, Pedro2, Juan, Juan3 .....

Upvotes: 1

Jose Silva
Jose Silva

Reputation: 13

I believe that this that you've commented is wrong...No Update. Only select: select * from profile n JOIN ( SELECT username, min_id, @row_number:=@row_number+1 as cntr FROM ( SELECT username, MIN(profile_id) min_id FROM profile GROUP BY username HAVING COUNT(*) > 1 ) AS t2 , (SELECT @row_number:=0) AS t ) d ON n.username = d.username

Upvotes: 0

Related Questions