File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes delete from select query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "delete from select query" Watch "delete from select query" New topic

delete from select query

Naadir Peterson
Ranch Hand

Joined: Sep 22, 2004
Posts: 37

I have a user table thats related to many tables... i need to delete all the data that's related to that user and data which are related to those tables etc... the thing is there's over 100 tables that i need to delete from... isn't there an easier way to delete everything related to that user without deleting the data in the table 1 by 1.
Is there any way i can delete from 1 big select statement?

I'm using MS SQL 2000...

Phani Kumar

Joined: Feb 15, 2002
Posts: 22
I didnt get your requirement, what exactly do you mean by deleting with a single select query? Obviously, Select query doesnt modify the database, it just returns the rows. As far as I know, you can use the ON DELETE CASCADE option available in database. This deletes all the related records from all the dependent tables when you delete the corresponding master record. Hope I am clear.
Stefan Willi
Ranch Hand

Joined: Mar 11, 2005
Posts: 47
If the purpose of deleting the tables is to start from scratch, then you can easily drop the user (in Oracle "drop <username>") and re-create him.

I do it so with my Oracle Test-DB.

Nischal Tanna
Ranch Hand

Joined: Aug 19, 2003
Posts: 182
write a stored procedure if u are using a java application to achieve the same.

Carol Enderlin
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
Looks like SQL Server 2000 supports "cascade delete".

Found this with a quick google search:Using Microsoft SQL Server Constraints article

Oops looks like someone beat me to mention this, but I'll leave the article reference.
[ March 15, 2005: Message edited by: Carol Enderlin ]
I agree. Here's the link:
subject: delete from select query
It's not a secret anymore!