Skip to content
Advertisement

Insert a record into database and associate it to a record in another table without the id

This may be a duplicate question, but I do not know the terminology to use to search for the answer.

I have an account table with the following fields:

  • id
  • email

And a widget table with the following field:

  • id
  • name
  • account_id

The account_id is set as foreign key and it references the account(id).

How do I insert a widget and associate it to an existing account record in a single query if only the account email is known?

Advertisement

Answer

Assuming account.id is an identity (or serial) column, you can use a writetable CTE:

with new_account as (
  insert into account (email) values ('....')
  returning id
)
insert into widget (name, account_id)
select id, 'new widget'
from new_account;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement