How to remove duplicates records from table with keep one and change key in other table?

Question

I have these two tables:

TableA
+----+----------+----------+
| Id | TableBId | TableCId |
+----+----------+----------+
|  1 |      1   |     10   |
|  2 |      2   |     11   |
|  3 |      3   |     12   |
|  4 |      4   |     13   |
|  5 |      5   |     14   |

TableC
+-----+------+
| Id  | Name |
+-----+------+
|  10 | John |
|  11 | John |
|  12 | Ann  |
|  13 | Mark |
|  14 | Mark |
+-----+------+

How can I remove duplicates records from TableC with keep one and change id in TableA?

Finally, the tables should look like:

TableA
+----+----------+----------+
| Id | TableBId | TableCId |
+----+----------+----------+
|  1 |      1   |     10   |
|  2 |      2   |     10   |
|  3 |      3   |     12   |
|  4 |      4   |     13   |
|  5 |      5   |     13   |

TableC
+-----+------+
| Id  | Name |
+-----+------+
|  10 | John |
|  12 | Ann  |
|  13 | Mark |
+-----+------+

At first, I need to change the ids, then remove the duplicates.

Hi and welcome to stackoverflow. This is not a replacement for web searches and tutorials. We can help you with specific problems with your code, but we don't re-type tutorials or write the code for you. Please see [ask]. Do some research, then [edit] your question and add your code as a [mcve]. What is the problem with it? What happens when you run it? What did you expect to happen instead? Any errors? Good luck! — Robert, Apr 25 '19 at 15:59
Check out Erwin Brandstettar's answer here: https://stackoverflow.com/questions/53366008/remove-duplicates-from-a-table-and-re-link-referencing-rows-to-the-new-master | Also: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=6fb3165d17acf2467093f278956c3fd6 — J Spratt, Apr 25 '19 at 17:19

score 1 · Accepted Answer · answered Apr 25 '19 at 17:49

First update table a by setting column cid to the minimum id from table c which has the same name as the name of the id equal to cid:

update a
set cid = (
  select min(id) from c where name = (select name from c where id = a.cid)
);

Then delete from table c all the rows with duplicate names keeping the one with the minimum id:

delete from c t where exists (
  select 1 from c where name = t.name and id < t.id
);

See the demo.
Results:
Table c

| id  | name |
| --- | ---- |
| 10  | John |
| 12  | Ann  |
| 13  | Mark |

Table a

| id  | cid |
| --- | --- |
| 1   | 10  |
| 2   | 10  |
| 3   | 12  |
| 4   | 13  |
| 5   | 13  |

How to remove duplicates records from table with keep one and change key in other table?

1 Answers1