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 |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2003-01-29 : 10:22:59
|
| Hello-I have a table structure setup as follows.tblSegments-----------segmentIDsegmentDesctblNotes-----------noteIDnoteDesctblSegmentsNotes----------------segmentIDnoteIDI need to create a sproc that deletes a segment. I have the segmentID that needs to be deleted. Because of FK constraints I will need to delete the appropriate segmentID and noteIDs from tblSegmentNotes first, but once I do that I don't have a way to reference the noteID's that need to be deleted anymore.Normally I would just do a "DELETE FROM tblNotes WHERE noteID NOT IN..." but the records in tblNotes aren't just related to segments, so that solution won't work. I could always use cursors and some sort of while loop, but their must be a better solution than that.Any help? Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 10:27:56
|
| If you're using SQL Server 2000, you can modify the foreign key constraints to include ON DELETE CASCADE actions. There's more info on this in Books Online under "CREATE TABLE" and "foreign keys".If you're using SQL 7.0 or earlier, you can use this:http://www.sqlteam.com/item.asp?ItemID=8595 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-29 : 10:31:43
|
| select noteID into #a from tblSegmentsNotes where segmentID = @segmentIDbegin trandelete tblSegmentsNotes where segmentID = @segmentIDdelete tblSegments where segmentID = @segmentIDdelete tblNotes from #a where #a.noteID = tblNotes.noteIDcommit tranwith error processing of course.Could use table variable instead of temp table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2003-01-29 : 10:37:49
|
| Thanks, I'll go with the Cascading Deletes. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-29 : 10:39:31
|
| By having the table tblSegmentsNotes you are saying that entries in tblSegments and tblNotes are independent.i.e. that a tblNotes entry can be linked to many tblSegments entries.Deleting tblNotes entries when the tblSegments entrty is deleted is implying that the relationship is 1 - many and that the structuer should betblSegments ----------- segmentID segmentDesc noteIDtblNotes ----------- noteID noteDesc making the delete simple.With the structure you have you should do the not in delete on notes - and this should be done when you decide that the notes should not be available for linking to segments.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
DELETE n-to-n