Copy data on parent and child table-CodePudding

I have two database tables. What I need to do is to copy specific data from one storage to another, but also keep the mapping to the photos. First part I can do easily writing

INSERT INTO item (storage_id, price, quantity, description, document_id)
SELECT 10, price, quantity, description, document_id
FROM item
WHERE quantity >= 10 AND price <= 100

but after that newly inserted items does not have photos. Note, that document_id field is unique for not copied items.

CodePudding user response：

Given that document_id is the same in the two sets, we can used that to ensure that after the first copy, that all duplicate entries that have photos are copied across.

Note: This is still a dirty hack, but it will work. Ideally with data synchronizations we make sure that there is a reference or common key in all the target tables. You could also use output parameters to capture the new id values or use a cursor or other looping constructs to process the records 1 by 1 and copy the photos at the same time instead of trying to update the photos after the initial copy stage.

This query will insert photos for items that do NOT have photos but another item with the same document_id does have photos.

INSERT INTO item_photo (item_id, "date", size)
SELECT "source_photo".item_id, "source_photo"."date", "source_photo". Size
FROM item "target_item"
INNER JOIN item "source_item" on "target_item".document_id = "source_item".document_id
INNER JOIN item_photo "source_photo" ON "source_item".id = "source_photo".item_id
WHERE "target_item".id <> "source_item".id
  AND NOT EXISTS ( SELECT id FROM item_photo WHERE item_id = "target_item".id)
  AND source_item.id IN (
                         SELECT MIN(p.item_id) as "item_id"
                         FROM item_photo p
                         INNER JOIN item i ON p.item_id = i.id
                         GROUP BY document_id
                        )

CodePudding user response：

Assuming id columns are auto-generated surrogate primary keys, like a serial or IDENTITY column.

Use a data-modifying CTE with RETURNING to make to with a single scan on each source table:

WITH sel AS (
   SELECT id, price, quantity, description, document_id
   FROM   item
   WHERE  quantity >= 10
   AND    price <= 100
   )
, ins_item AS (
   INSERT INTO item
         (storage_id, price, quantity, description, document_id)
   SELECT 10        , price, quantity, description, document_id
   FROM   sel
   RETURNING id, document_id  -- document_id is UNIQUE in this set!
   )
INSERT INTO item_photo
      (item_id,    date,    size)
SELECT ii.id  , ip.date, ip.size
FROM  ins_item   ii
JOIN  sel        s  USING (document_id)   -- link back to org item.id
JOIN  item_photo ip ON ip.item_id = s.id; -- join to org item.id

CTE sel reads all we need from table items.

CTE ins_item inserts into table item. The RETURNING clause returns newly generated id values, together with the (unique!) document_id, so we can link back.

Finally, the outer INSERT inserts into item_photo. We can select and combine with according new item.id after linking back to the original item.id.

Insert New Foreign Key Row for new Strings

But:

document_id field is unique for not copied items.

Does that guarantee we are dealing with unique document_id values?