I have two tables in Oracle, job and reference.
I want to insert a new record into both tables, with a key generated from a sequence. Something like:
insert into (
select j.jobid, j.fileid, j.jobname, r.reffileid
from job j
inner join reference r on j.jobid=r.jobid)
values (jobidsequence.nextval, 4660, 'name', 4391);
ORA-01776: cannot modify more than one base table through a join view
You can use a side-effect of the
insert all multi-table insert syntax for this:
insert all into job (jobid, fileid, jobname) values (jobidsequence.nextval, fileid, jobname) into reference (jobid, reffileid) values (jobidsequence.nextval, reffileid) select 4660 as fileid, 'name' as jobname, 4391 as reffileid from dual; 2 rows inserted. select * from job; JOBID FILEID JOBNAME ---------- ---------- ---------- 42 4660 name select * from reference; JOBID REFFILEID ---------- ---------- 42 4391
From the restrictions:
You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.
Clearly I am using a sequence in the
values clause, so the first sentence doesn't seem quite accurate; but you can't use it in the
select part. (I'm not 100% sure if it can be used in the
values in all versions, but the documentation is a little misleading in any case, and contradicts itself).
So I'm taking advantage of the fact that because it is a single statement, the two references to
nextval get the same number, as the third sentence says, so the same sequence value is used in both tables.