2

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.

butteredtoast
  • 21
  • 3
  • I think this answer to a very similar question is quite elegant: http://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key/46477#46477 Note: not my answer there but Erwin's (the code under the INSERT missing FK rows at the same time title). – ypercubeᵀᴹ Jun 02 '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 02 '15 at 15:09
  • @ypercube I agree, it does what I'm looking for well, thank you! – butteredtoast Jun 04 '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 04 '15 at 12:43

0 Answers0