0

Consider 2 tables (table A and table B) with a many-to-many relationship, each containing a primary key and other attributes. To map this relation there's a third joint table (table C) containing the foreign keys for each table of the relation ( fk_tableA | fk_tableB ).

Table B contains duplicate rows (except for the pk), so I want to merge these together into a single record with whatever unique primary key, just like so:

table B                   table B (after merging duplicates)
1 | Henry | 100.0         1 | Henry | 100.0
2 | Jessi | 97.0          2 | Jessi | 97.0
3 | Henry | 100.0         4 | Erica | 11.2
4 | Erica | 11.2

By merging these records, there may be foreign keys of table C (joint table) pointing to primary keys of table B that no longer exist. My goal is to edit them to point to the merged record:

Before merging:

tableA         table B               table C

id | att1      id | att1 | att2      fk_A | fk_b
-----------   -------------------    ------------
 1 | ab123     1 | Henry | 100.0        1 | 1 
 2 | adawd     2 | Jessi | 97.0         2 | 3  
 3 | da3wf     3 | Henry | 100.0    
               4 | Erica | 11.2

On table C, 2 records from table B are referenced (1 and 3) which happen to be duplicated rows. My goal is to merge those into a single record (in table B) and update the foreign key in table C:

After merging:

tableA         table B               table C

id | att1      id | att1 | att2      fk_A | fk_b
-----------   -------------------    ------------
 1 | ab123     1 | Henry | 100.0        1 | 1 
 2 | adawd     2 | Jessi | 97.0         2 | 1
 3 | da3wf     4 | Erica | 11.2   

- Note that id=3 was merged/deleted from table B and the same id
  was updated on table C to point to the merged record's id.

So my question is basically how to update a junction table upon merging records of a table? I am currently using Postgres and working on millions of data.

GRoutar
  • 1,311
  • 1
  • 15
  • 38

1 Answers1

1
-- \i tmp.sql

CREATE TABLE persons
        ( id integer primary key
        , name text
        , weight decimal(4,1)
        );

INSERT INTO persons(id,name,weight)VALUES
 (1 ,'Henry', 100.0)
 ,(2 ,'Jessi', 97.0)
 ,(3 ,'Henry', 100.0)
 ,(4 ,'Erica', 11.)
        ;

CREATE TABLE junctiontab
        ( fk_A integer NOT NULL
        , p_id integer REFERENCES persons(id)
        , PRIMARY KEY (fk_A,p_id)
         );

INSERT INTO junctiontab(fk_A, p_id)VALUES (1 , 1 ),(2 , 3  );

        -- find the ids of affected persons.
        -- [for simplicity: put them in a temp table]
CREATE TEMP table xlat AS
SELECT * FROM(
        SELECt id AS wrong_id
                ,min(id) OVER (PARTITION BY name ORDER BY id) AS good_id
        FROM persons p
        ) x
WHERE good_id <> wrong_id
        ;

        --show it
SELECT *FROM xlat;

UPDATE junctiontab j
SET p_id = x.good_id
FROM xlat x
WHERE j.p_id = x.wrong_id
        -- The good junction-entry *could* already exist...
AND NOT EXISTS (
        SELECT *FROM junctiontab nx
        WHERE nx.fk_A= j.fk_A
        AND nx.p_id= x.good_id
        )
        ;

DELETE FROM junctiontab d
        -- if the good junction-entry already existed, we can delete the wrong one now.
WHERE EXISTS (
        SELECT *FROM junctiontab g
        JOIN xlat x ON g.p_id= x.good_id
                   AND d.p_id = x.wrong_id
        WHERE g.fk_A= d.fk_A
        )
        ;

        --show it
SELECT *FROM junctiontab
        ;

        -- Delete thewrongperson-records
DELETE FROM persons p
WHERE EXISTS (
        SELECT *FROM xlat x
        WHERE p.id = x.wrong_id
        );

        --show it
SELECT * FROM persons p;

Result:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 2
SELECT 1
 wrong_id | good_id 
----------+---------
        3 |       1
(1 row)

UPDATE 1
DELETE 0
 fk_a | p_id 
------+------
    1 |    1
    2 |    1
(2 rows)

DELETE 1
 id | name  | weight 
----+-------+--------
  1 | Henry |  100.0
  2 | Jessi |   97.0
  4 | Erica |   11.0
(3 rows)
wildplasser
  • 43,142
  • 8
  • 66
  • 109