Sunday, March 25, 2012

Deleting from three tables

I'm triyng to delete from three tables.

Issue Table:

Column Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Unchecked

Outlook Table:

Column Name Data Type Allow Nulls ID int Unchecked menu bit Checked mnuOrder int Checked mnuLevel int Checked parent int Checked issueID int Unchecked masterPage varchar(100) Unchecked visible bit Checked name varchar(50) Checked title varchar(250) Unchecked description varchar(1000) Checked summary text Checked contents text Checked image varchar(50) Checked imgCaption varchar(1000) Checked approve bit Checked createDate datetime Unchecked

Links Table:

Column Name Data Type Allow Nulls lnkID int Unchecked linkFromID int Unchecked linkToID int Unchecked

Issue table contais all the magazine issues. Outlook table contains all the pages of the Issue table. And the Links table contains the links or connection between parent page and child page. So here's what I wanted to do. When I click the delete issue button, I want to delete any pages, links, and issue from three tables that matches the issue ID that I wanted to delete. So for example, if I wanted to delete issueID 2, all the pages in the Outlook table and any links of those pages(lnkFromID) that are in the Links table should be deleted too. The lnkFromID and lnkToID are foriegn key of Outlook.ID table. The lnkFromID is the parent and lnkToID is the child.

I was wondering that maybe I can three delete statements for three tables. Is this a possibility? So any help is much appreciated.

Again, help is still needed. It seems to me that these statements will delete from three tables.

DELETE * FROM [OLlinks],[Outlook] WHERE ([OLlinks].[linkToID] = [Outlook].[ID] AND [Outlook].[issueID] = @.ID)

DELETE * FROM [OLissue],[Outlook] WHERE ([OLissue].[issueID] = [Outlook].[issueID] AND [OLissue].[issueID] = @.ID)

However, is this a best practice and how do I execute two delete statemens in one call?

No comments:

Post a Comment