CODEWITHSUNDEEP
phpmysql
Sandy DK
Sandy DK

Reputation: 83

Update 2nd duplicate value in mysql table?

i have 52k rows of records in my user table. In that table user_hash column having approximately 2000 duplicate values. i want to update user_hash columns 2nd duplicate value. The following table user_id (3, 10), (5, 14) having same value and i want to update user_id 10 and 14 values. how can i update it with MySQL Qry?

--------------------------------
user_id user_hash   user_status
--------------------------------
1   ae57250b    1
2   310cb4e0    1
3   28b365c7    1
4   0073265b    1
5   8bec42a9    1
6   00a5c443    1
7   e1c27b19    1
8   993dc301    1
9   8fc8a6bf    1
10  28b365c7    1
11  194714c0    1
12  4611d83a    1
13  277a426b    1
14  8bec42a9    1
15  740c1412    1
... ...     ...
... ...     ...
... ...     ...

The following qry which i have used to check duplicate entry

SELECT user_hash, COUNT(user_hash) 
FROM user_data 
GROUP BY user_hash 
HAVING COUNT(user_hash) > 1;

Upvotes: 3

Views: 97

Answers (1)

mikey
mikey

Reputation: 1313

The first thing that comes to mind is that you may want to define a constraint such that in the future you can't have non-unique values where you don't want them.

The second thing is to ensure that these hashes can't legitimately collide/overlap.

Those thoughts aside (as they may be irrelevant to your use case):

update user_data set user_data.user_hash = STORED_PROC_FOR_HASH()
from (
    select *, row_number() over(partition by user_hash order by (select null)) row_num  
    from user_data 
) user_data 
where row_num > 1

Source for the above query: https://stackoverflow.com/a/25090251/3080207

As hinted at by Nick, you'll need to be able to generate a unique hash, which is pretty much the 2nd component to this problem.

Hopefully that is helpful.

Upvotes: 1

Related Questions