November 10, 2015 at 12:47 am
I've Table And Data as follow,
CREATE TABLE [dbo].[myMenuCollection](
[menuCollection_idx] [int] NOT NULL,
[parentID] [int] NULL,
[menuNme] [nvarchar](200) NULL,
[navigateURL] [nvarchar](100) NULL,
CONSTRAINT [PK_myMenuCollection] PRIMARY KEY CLUSTERED
(
[menuCollection_idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (1, 0, N'[Imbangan Duga]', N'#')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (2, 0, N'[Penyata Kewangan]', N'#')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (3, 2, N'[Penyata Kewangan] - [Perbandingan Tahun]', N'123.aspx')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (4, 0, N'[Penyata Belanjawan]', N'#')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (5, 0, N'[Penyata Aliran Tunai]', N'#')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (6, 5, N'[Penyata Aliran Tunai] - [Perbandingan Tahun]', N'#')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (7, 5, N'[Penyata Aliran Tunai] - [Perbandingan Kumpulan Wang]', N'345.aspx')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (8, 0, N'[Penyata Lejar Am]', N'#')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (9, 0, N'[Penyata Gaji]', N'gaji.aspx')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (10, 9, N'[Penyata Gaji] - [LPG]', N'123.aspx')
INSERT [dbo].[myMenuCollection] ([menuCollection_idx], [parentID], [menuNme], [navigateURL]) VALUES (11, 10, N'[Penyata Gaji] - [LPG] - [Staf Akademik]', N'23.aspx')
1- Let's say I'm delete - menuCollection_idx = 1. Based on row, any row hold parentID = 1 must automatically Deleted
2- Let's say I'm delete - menuCollection_idx = 2. Based on row, any row hold parentID = 2 must automatically Deleted. In this case, we also need to delete menuCollection_idx=3. It's because, the parentID = 2
3- Let's say I'm delete - menuCollection_idx = 9. Based on row, any row hold parentID = 9 must automatically Deleted. In this case, we also need to delete menuCollection_idx=10. It's because, the parentID = 9. Then, we also need to delete menuCollection_idx=11. It's because, the parentID = 10. This delete operation is more than 1 level
I'm stuck
Please help me
November 10, 2015 at 1:23 am
You can do it with recursive CTE. Here is an example:
select * from myMenuCollection
declare @RecToDel int = 9;
With RecordsToDelete as (
SELECT menuCollection_idx
FROM dbo.myMenuCollection
WHERE menuCollection_idx = @RecToDel
UNION ALL
SELECT mMC.menuCollection_idx
FROM dbo.myMenuCollection as mMC INNER JOIN RecordsToDelete RTD ON mMC.parentID = RTD.menuCollection_idx)
DELETE mMC
FROM dbo.myMenuCollection mMC INNER JOIN RecordsToDelete RTD ON mMC.menuCollection_idx = RTD.menuCollection_idx
go
select * from myMenuCollection
go
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 10, 2015 at 7:32 am
It's work. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply