CODEWITHSUNDEEP
sqlpostgresqlduplicates
panmigal
panmigal

Reputation: 111

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

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.

Upvotes: 0

Views: 50

Answers (1)

forpas
forpas

Reputation: 164099

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  |

Upvotes: 1

Related Questions