I have a table in which a couple of the IDs have different values in the name column.
| ID | Name | col_3 | col_4 |
|---|---|---|---|
| 1 | name_1 | x_1 | y_1 |
| 2 | name_2 | x_2 | y_2 |
| 3 | name_3 | x_3 | y_3 |
| 4 | name_4 | x_4 | y_4 |
| 1 | other_name_1 | x_5 | y_5 |
| 2 | other_name_2 | x_6 | y_6 |
| 5 | name_5 | x_7 | y_7 |
I want to substitute ‘other_name_1’ with ‘name_1’ / ‘other_name_2’ with ‘name_2’ etc. It doesn’t matter which ‘name’ is selected, any option is fine as long as the same name is used for every corresponding ID.
The desired output of a select statement would be:
| ID | Name | col_3 | col_4 |
|---|---|---|---|
| 1 | name_1 | x_1 | y_1 |
| 2 | name_2 | x_2 | y_2 |
| 3 | name_3 | x_3 | y_3 |
| 4 | name_4 | x_4 | y_4 |
| 1 | name_1 | x_5 | y_5 |
| 2 | name_2 | x_6 | y_6 |
| 5 | name_5 | x_7 | y_7 |
>Solution :
select id
,min(name) over(partition by id) as name
,col_3
,col_4
from t
| id | name | col_3 | col_4 |
|---|---|---|---|
| 1 | name_1 | x_1 | y_1 |
| 1 | name_1 | x_5 | y_5 |
| 2 | name_2 | x_6 | y_6 |
| 2 | name_2 | x_2 | y_2 |
| 3 | name_3 | x_3 | y_3 |
| 4 | name_4 | x_4 | y_4 |
| 5 | name_5 | x_7 | y_7 |