Home » SQL & PL/SQL » SQL & PL/SQL » Using WITH clause with DML Statements (Oracle 10g)
Using WITH clause with DML Statements [message #292013] Mon, 07 January 2008 07:59 Go to next message
pratyushpushkar
Messages: 2
Registered: August 2007
Location: Chicago
Junior Member

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
Re: Using WITH clause with DML Statements [message #292016 is a reply to message #292013] Mon, 07 January 2008 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With clause is part of select statement and so must be bewteen select and insert.
SQL> insert into t (C1)
  2  with d as (select empno from emp)
  3  select empno from d
  4  /

14 rows created.


Regards
Michel
Re: Using WITH clause with DML Statements [message #292020 is a reply to message #292013] Mon, 07 January 2008 08:16 Go to previous messageGo to next message
Clauddvon
Messages: 23
Registered: July 2006
Junior Member
Here's a link to info on the WITH clause including examples that may help.

http://www.dba-oracle.com/t_sql99_with_clause.htm
Re: Using WITH clause with DML Statements [message #292035 is a reply to message #292013] Mon, 07 January 2008 09:14 Go to previous message
pratyushpushkar
Messages: 2
Registered: August 2007
Location: Chicago
Junior Member

Thanks Michel, it worked.
Previous Topic: how to reject trailing spaces?
Next Topic: merge - how to idenitfy the number of rows inserted and updated
Goto Forum:
  


Current Time: Sun Dec 04 02:31:56 CST 2016

Total time taken to generate the page: 0.10544 seconds