Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Stanley Tan
Starting Member
25 Posts |
 Posted - 2002-06-03 : 22:47:09
|
| How can I delete a record and all the child records? I know this can be done simply by changing the constraints, but can this be done simply by using SQL?e.g. DELETE (THIS AND ALL CHILD RECORDS) FROM ... WHERE ... |
|
|
ailuro
Starting Member
8 Posts |
Posted - 2002-06-03 : 23:05:34
|
| I know this isn't much of a help, but I if there is a way to do it, I haven't found it. Best thing I've found to do is put it all into a stored proc and do a giant round of DELETEs in order of dependance.-=Ailuro=- |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-03 : 23:09:27
|
| Well, you might not believe this, but if you POSTED YOUR TABLE STRUCTURES SO WE DON'T HAVE TO GUESS we might be able to figure this out for you. |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-06-03 : 23:46:12
|
Actually, this was a "in-general" question. But let's assume we have a tables like:Forums------ForumIDForumNameMessages--------MessageIDForumIDMessageBodyAssuming there are rows in Messages that reference ForumID '1', and I want to delete ForumID '1', how can I do this using SQL?quote:
Well, you might not believe this, but if you POSTED YOUR TABLE STRUCTURES SO WE DON'T HAVE TO GUESS we might be able to figure this out for you.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-03 : 23:54:56
|
| In SQL 2000, you can create a foreign key with a CASCADE DELETE action. So if you delete ForumID 1, it will remove all of the child rows in the messages table automatically.If you're using SQL 7.0 or earlier, you can write a trigger that deletes the child rows from the child table whenever the parent table gets deleted. There are examples of this code on SQL Team if you do a forum search, and also in the Inside SQL Server books by Microsoft Press.Lastly, you can use this code:http://www.sqlteam.com/item.asp?ItemID=8595As long as you have foreign keys declared between tables, that code will peform the cascade delete. |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-06-04 : 04:44:10
|
| Thanks for the help! :) So there is no way of doing this through SQL without changing the foreign key constraints? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-04 : 07:46:09
|
| The trigger option will work if you don't have foreign keys defined between tables. However, if you truly want to maintain this parent-child relationship you need to define foreign keys or you'll end up with funky data. |
|
|
|
|
|
|
|
|
DELETE