Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

delete from select query

 
Naadir Peterson
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
 
Phani Kumar
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.
 
Stefan Willi
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
 
Nischal Tanna
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.
 
Carol Enderlin
drifter
Ranch Hand
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic