the scenario is as follows:

I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier table, first insert into supplier then use the new supplier_id in invoice, else use the supplier_id of the matching supplier_name.

I've managed to get here:

insert into supplier (supplier_company) select ('Company Name') where not exists (select 1 from supplier where supplier_company='Company Name');
insert into invoice (invoice_number, supplier_id) values (xxx, (select supplier_id from supplier where supplier_company = 'Company Name')

but I was wondering if there's a cleaner, clearer way to express this in one expression.

Thank you for your time.

  • I think this answer to a very similar question is quite elegant: dba.stackexchange.com/questions/46410/… Note: not my answer there but Erwin's (the code under the INSERT missing FK rows at the same time title). – ypercubeᵀᴹ Jun 2 '15 at 11:51
  • A single statement can change at most one table. So it is impossible for one expression to insert rows into two tables. – Andomar Jun 2 '15 at 15:09
  • @ypercube I agree, it does what I'm looking for well, thank you! – butteredtoast Jun 4 '15 at 11:57
  • 1
    @Andomar in Postgres, you can insert into / update / delete from 2 (or more) tables in one statement, using CTEs. – ypercubeᵀᴹ Jun 4 '15 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.