[ACCEPTED]-Oracle Insert via Select from multiple tables where one table may not have a row-insert

Accepted answer

Score: 25

Outter joins don't work "as expected" in 9 that case because you have explicitly told 8 Oracle you only want data if that criteria 7 on that table matches. In that scenario, the 6 outter join is rendered useless.

A work-around

INSERT INTO account_type_standard 
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES( 
  (SELECT account_type_standard_seq.nextval FROM DUAL),
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?), 
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

[Edit] If 5 you expect multiple rows from a sub-select, you 4 can add ROWNUM=1 to each where clause OR 3 use an aggregate such as MAX or MIN. This 2 of course may not be the best solution for 1 all cases.

[Edit] Per comment,

  (SELECT account_type_standard_seq.nextval FROM DUAL),

can be just

  account_type_standard_seq.nextval,

Score: 8

A slightly simplified version of Oglester's 2 solution (the sequence doesn't require a 1 select from DUAL:

INSERT INTO account_type_standard   
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES(   
  account_type_standard_seq.nextval,
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?),
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

Score: 3

It was not clear to me in the question if 11 ts.tax_status_code is a primary or alternate 10 key or not. Same thing with recipient_code. This 9 would be useful to know.

You can deal with 8 the possibility of your bind variable being 7 null using an OR as follows. You would bind 6 the same thing to the first two bind variables.

If 5 you are concerned about performance, you 4 would be better to check if the values you 3 intend to bind are null or not and then 2 issue different SQL statement to avoid the 1 OR.

insert into account_type_standard 
(account_type_Standard_id, tax_status_id, recipient_id)
(
select 
   account_type_standard_seq.nextval,
   ts.tax_status_id, 
   r.recipient_id
from tax_status ts, recipient r
where (ts.tax_status_code = ? OR (ts.tax_status_code IS NULL and ? IS NULL))
and (r.recipient_code = ? OR (r.recipient_code IS NULL and ? IS NULL))

Score: 3

Try:

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
       ts.tax_status_id, 
       ( select r.recipient_id
         from recipient r
         where r.recipient_code = ?
       )
from tax_status ts
where ts.tax_status_code = ?

Score: 1

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
   ts.tax_status_id, 
   ( select r.recipient_id
     from recipient r
     where r.recipient_code = ?
   )
from tax_status ts
where ts.tax_status_code = ?

Source: stackoverflow.com

[ACCEPTED]-Oracle Insert via Select from multiple tables where one table may not have a row-insert

Grant Select, Insert, Update to a Tablespace

MySQL INSERT with multiple nested SELECTs

postgresql multiple insert with select query

sqlite3 insert and read BLOB data in database

How to use 'select ' in MySQL 'insert' statement

Oracle insert failure : not a valid month

What is better? Subqueries or inner joining ten tables?

ORACLE SQL get difference of two values retrieved from 2 select statements

SQL select & group by a period of time (timestamp)

how to set a null value to a field in a table using oracle sql developer tool?