jQuery in Action, 3rd edition
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

Win a copy of Introducing JavaFX 8 Programming this week in the JavaFX forum!
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: http://aspose.com/file-tools
subject: delete from select query
It's not a secret anymore!