i have two columns in my database "Name" and "Id"
e.g.
NAME | ID
-----+-----------
jhon | 0
-----+-----------
kim | 0
-----+-----------
alex | 0
-----+-----------
jhon | 0
-----+-----------
alex | 0
-----+-----------
max | 0
-----+-----------
Currently i have assigned "id" 0 to all. i want to write a stored procedure or query that assign the same id (any random value) to the records that are occurring more than one time. Other record's ID remain 0 . for example john and Alex are occurring twice. so their id should be update else other record's ID remain same . here is my sample and desired out put
NAME | ID
-----+-----------
jhon | 25
-----+-----------
kim | 0
-----+-----------
alex | 12
-----+-----------
jhon | 25
-----+-----------
alex | 12
-----+-----------
max | 0
-----+-----------
I'd use a group by clause to get all the IDs, like so:
You can then use this
select
in a simpleupdate
command, that sets the ID for every row in this result set.A more complete example might look something like this: