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.
2
Answers
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.This query will insert photos for items that do NOT have photos but another item with the same
document_id
does have photos.Assuming
id
columns are auto-generated surrogate primary keys, like aserial
orIDENTITY
column.Use a data-modifying CTE with
RETURNING
to make do with a single scan on each source table:CTE
sel
reads all we need from tableitems
.CTE
ins_item
inserts into table item. TheRETURNING
clause returns newly generatedid
values, together with the (unique!)document_id
, so we can link back.Finally, the outer
INSERT
inserts intoitem_photo
. We can select matching rows after linking back to the originalitem.id
.Related:
But:
Does that guarantee we are dealing with unique
document_id
values?