sql - Update table with different values from a different table which is grouped

Question

I have a oracle table called table1 that has a foreign key from table2. table2 has 2 columns: id, name table2.name has a lot of duplicates that need to be sorted out, so I grouped table2 by name and kept only 1 id for each name. However, table1.table2_id uses a lot of the duplicated fields.

How can I change all the table1.table2_id fields so that there are no duplicate names?

Currently: table1:

id	blabla	table2_id
1	row	1001
2	row	1002
3	row	1003
4	row	1004
5	row	1004
6	row	1005

question from:https://stackoverflow.com/questions/65906136/update-table-with-different-values-from-a-different-table-which-is-grouped

Answer 1

Here are syntactically correct Oracle statements

update table1 t1
set t1.table2_id = (
  select new_id
  from (
    select id, min(id) over (partition by name) new_id
    from table2
  ) d
  where d.id = t1.table2_id
);

delete from table2
where id not in (
  select min(id) from table2 group by name
);

The analytic function min(id) over (partition by name) is used here so that you can have all original ids together with their new ids (the min ids from the set where the name is the same).