• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

delete from select query

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic