Update duplicate column values in PostgreSQL adding a number at end

Question

Im trying to update the duplicate usernames in my PostgreSQL database by adding a number at the end of the duplicates. curently my users table is as follows:

| user_id | username |
|---------|----------|
| 1       | mike     |
| 2       | mike     |
| 3       | susan    |
| 4       | susan    |
| 5       | susan    |
| 6       | joe      |

And the expected result is:

| user_id | username |
|---------|----------|
| 1       | mike     |
| 2       | mike2    |
| 3       | susan    |
| 4       | susan2   |
| 5       | susan3   |
| 6       | joe      |

I've been trying to use some expressions found here in StackOverflow but i keep getting a SQL syntax error.

user330315 · Accepted Answer · 2020-10-02 14:00:52Z

5

You can update from a sub-query:

update users
   set username = concat(t.username, t.rn)
from (
  select user_id, username, 
         row_number(*) over (partition by username order by user_id) as rn
  from users
) t
where t.user_id = users.user_id
  and t.rn > 1;

Collectives™ on Stack Overflow

Update duplicate column values in PostgreSQL adding a number at end

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related