Home » SQL & PL/SQL » SQL & PL/SQL » Delete orphaned childs
Delete orphaned childs [message #269478] Sat, 22 September 2007 14:19 Go to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
I am using a query to find all the id:s of orphaned childs.

I want to delete all of these so I can create a foreign key.

The primary key in the parent table is a composite key.

I find the keys in this way:


select distinct column1,column2 from table1
minus
select distinct table1_column1,table1_column2 from table2_table1


and I would like something like:

delete from table2_table1 where table1_column1,table1_column2 in (
select distinct column1,column2 from table1
minus
select distinct table1_column1,table1_column2 from table2_table1
)


But that isn't working.
How should I do this?

Kind regards
Emil

Re: Delete orphaned childs [message #269480 is a reply to message #269478] Sat, 22 September 2007 14:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>But that isn't working.
My car is not working.
Tell me how to make my car work.

Please read & follow posting guidelines as stated in the #1 STICKY post at the very top entry in this forum.
Re: Delete orphaned childs [message #269481 is a reply to message #269478] Sat, 22 September 2007 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"distinct" is useless when using set operator ("union","minus","intersect").

use:
delete table1
where (column1,column2) in (<your select>)
/

Regards
Michel
Re: Delete orphaned childs [message #269483 is a reply to message #269478] Sat, 22 September 2007 15:07 Go to previous messageGo to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Oh, sorry for not posting the error.
I get an ora-00920 when executing the above sql. But my guess is that I am totally wrong when I am building the sql...

Re: Delete orphaned childs [message #269484 is a reply to message #269478] Sat, 22 September 2007 15:12 Go to previous messageGo to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Thank you, when using the sql without distinct it's working fine.
Re: Delete orphaned childs [message #269485 is a reply to message #269483] Sat, 22 September 2007 15:13 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Edit: I posted this before reading your message.

By
Vamsi

[Updated on: Sat, 22 September 2007 15:14]

Report message to a moderator

Previous Topic: Uses of materialized views?
Next Topic: Cursor with %ROWTYPE
Goto Forum:
  


Current Time: Fri Dec 09 04:07:31 CST 2016

Total time taken to generate the page: 0.10583 seconds