OraFAQ Forum: SQL & PL/SQL » Delete using a CTE

Home » SQL & PL/SQL » SQL & PL/SQL » Delete using a CTE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete using a CTE [message #630914]

Tue, 06 January 2015 04:51

system243trd
Messages: 5
Registered: January 2015

Junior Member

Is it possible to delete rows using a CTE e.g.

with CTE
( select ...
...
)

Delete r from rows

Report message to a moderator

Re: Delete using a CTE [message #630915 is a reply to message #630914]

Tue, 06 January 2015 04:59

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

CTE? What is CTE?

You can delete through an inline view, if this the question like:

SQL> delete (select e.rowid from emp e where deptno=(select deptno from dept where dname='ACCOUNTING'));

3 rows deleted.

Report message to a moderator

Re: Delete using a CTE [message #630920 is a reply to message #630915]

Tue, 06 January 2015 05:45

system243trd
Messages: 5
Registered: January 2015

Junior Member

A CTE is a common table expression.

Is it possible to do the following:

with cte (
select ..
...
..
)
delete (select rowid from table1)

Report message to a moderator

Re: Delete using a CTE [message #630921 is a reply to message #630914]

Tue, 06 January 2015 05:51

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

system243trd wrote on Tue, 06 January 2015 16:21

Is it possible to delete rows using a CTE

No. At least not the way you intend to do it. You could use the result set of the common table expression, but that would be unnecessary.

Why do you want to do it anyway?

Report message to a moderator

Re: Delete using a CTE [message #630922 is a reply to message #630920]

Tue, 06 January 2015 05:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

system243trd wrote on Tue, 06 January 2015 17:15

Is it possible to do the following:

with cte (
select ..
...
..
)
delete (select rowid from table1)

No. You could do it as -

Delete from table where ....(<your CTE>)

But once again, why do you want to do it this way?

Report message to a moderator

Re: Delete using a CTE [message #630925 is a reply to message #630920]

Tue, 06 January 2015 06:00

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

A CTE is a common table expression.

Ah! this is a MS/SQL notion, Oracle calls it (subquery) factoring clause.

Report message to a moderator

Re: Delete using a CTE [message #630936 is a reply to message #630925]

Tue, 06 January 2015 07:21

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I am not sure, but after google search it seems like the delete statement goes along CTE(subquery factoring) in MS SQL Server. Which is not the case in Oracle, where only a SELECT goes along with the expression.

Ed : oops missed MS SQL Server

[Updated on: Tue, 06 January 2015 07:25]

Report message to a moderator

Previous Topic:	Sql querry needed for scenario
Next Topic:	SYS_CONTEXT('USERENV','TERMINAL') gives "unknown" result in SQL Developer tool[split]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 13:16:06 CDT 2024