Using WITH clause with DML Statements [message #292013] |
Mon, 07 January 2008 07:59  |
|
Hi All,
I have a doubt regarding the utility of WITH clause with DML statements like UPDATE or INSERT?
For example, say I create a named query for a huge query that I have written.
WITH namedQuery
AS
(
SELECT row_number,a,b,c............ from table ta,tb,tc,td
)
Now, I have to join the namedQuery with the same dataset
i.e. select * from namedQuery, namedQuery n2 where
namedQuery.row_number=n2.rownumber+1;
And then I want to insert the dataset created above into a table.
INSERT INTO newTable
select * from namedQuery, namedQuery n2 where
namedQuery.row_number=namedQuery.rownumber+1;
Unfortunately, this doesn't work and gives errors. Could you please help me with this? If WITH clause has the restriction, do we have any other way of doing it?
Thanks,
Pratyush
|
|
|
|
|
|