• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Tim Holloway
  • Carey Brown
  • salvin francis

delete from select query

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
write a stored procedure if u are using a java application to achieve the same.
 
drifter
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Something about .... going for a swim. With this tiny ad ...
create, convert, edit or print DOC and DOCX in Java
https://products.aspose.com/words/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!