CODEWITHSUNDEEP
postgresql
Christian
Christian

Reputation: 318

How to include the values of a select statement in an insert? (PostgreSQL)

I have this select statement:

select it.item_id
from item as it
where it.owning_collection in (select col.collection_id
from collection as col
where col.name like '%Restricted%'

Which returns around 3k results. Now I would like to make an insert in another table for each one of those results with that item_id as one of the parameters like this:

insert into metadatavalue (metadata_value_id, **item_id**, metadata_field_id, text_value, text_lang, place, confidence)

But since I'm not very experienced with databases, I'm not sure how to make these multiple inserts

All the other information needed in the insert statement are fixed values.

Table structures:

Table Item
*item_id
*submitter_id
*in_archive
*withdrawn
*last_modified
*owning_collection
*dicoverable

Table metadata
*metadata_value_id
*item_id
*metadata_field_id
*text_value
*text_lang
*place
*authority
*confidence

Upvotes: 0

Views: 64

Answers (2)

ksa
ksa

Reputation: 403

insert into metadatavalue (metadata_value_id, item_id, metadata_field_id, text_value, text_lang, place, confidence)
select 'metadata_value_id',it.item_id,'metadata_field_id','text_value', 'text_lang', 'place', 'confidence'
from item it
where it.owning_collection in (select col.collection_id
from collection as col
where col.name like '%Restricted%')

Replace 'apostrophed' columns with its default values.

Further reading.

Upvotes: 1

Patrick
Patrick

Reputation: 32159

INSERT INTO metadatavalue (item_id, metadata_field_id, text_value, text_lang, place, confidence)
  SELECT it.item_id, <c1>, <c2>, <c3>, <c4>, <c5>
  FROM item AS it
  JOIN collection AS col ON col.collection_id = it.owning_collection
  WHERE col.name LIKE '%Restricted%'

Where you replace <c1> etc with your constant values. Note also that I have rewritten your SELECT query to a more efficient JOIN.

Upvotes: 1

Related Questions