CODEWITHSUNDEEP
sqloracle-databasesubquerycommon-table-expression
Jay
Jay

Reputation: 15

Issue with insert after subquery - Oracle

I've a sub list of subqueries something like below 

WITH QRY1 AS (.. SOME PL SQL STATEMNT),
QRY2 (.. SELECT X,Y,Z,QRY1.* ),
QRY3 (.. SOME SELECT * AGAIN USING QRY2)

and finally 

SELECT * FROM QRY3;

is there a way that I can do 

INSERT INTO table_name (a,b,c,d)
     SELECT * FROM QRY3;

Upvotes: 1

Views: 37

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You just need to put the CTE as part of the select, after the insert itself, rather then before it:

INSERT INTO table_name (a,b,c,d)
WITH QRY1 AS (.. SOME PL SQL STATEMNT),
QRY2 (.. SELECT X,Y,Z,QRY1.* ),
QRY3 (.. SOME SELECT * AGAIN USING QRY2)
 -- and finally 
SELECT * FROM QRY3;

Quick demo:

create table table_name (a number, b number, c number, d number);

insert into table_name (a,b,c,d)
with qry1 as (select 4 as d from dual),
qry2 as (select 2 as b, 3 as c, qry1.* from qry1),
qry3 as (select 1 as a, qry2.* from qry2)
select * from qry3;

1 row inserted.

select * from table_name;

         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3          4

Upvotes: 1

Related Questions