delete from select query

[Naadir Peterson]

Hi

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...

Thanks

[Phani Kumar]

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]

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.

Stefan

[Nischal Tanna]

write a stored procedure if u are using a java application to achieve the same.

[Carol Enderlin]

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 ]