The ID column has multiple values in the name column-CodePudding

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

CodePudding user response：

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

Fiddle