Geek Answers Handbook

SQL: DELETE data from a self-regulatory table in a specific order

I have a table like this:

groupId guid PK
parentId guid
name
left int
right int

There is a foreign key from parentId to groupId (this is a self-regulating table).

left and right MPTT values ​​left / right to maintain hierarchy. It is important to note that the larger the value on the left, the more deeply the element is nested (in other words: for any given element, its left value will always be greater than the left value of all parents).

I am trying to write a quick DELETE statement to remove everything except the topmost group (which always has an empty GUID), for example:

DELETE FROM [group] WHERE [groupId] <> '00000000-0000-0000-0000-000000000000'`

, , , . DELETE- , , , , .

, , :

DELETE FROM [group] WHERE [groupId] <> '00000000-0000-0000-0000-000000000000' 
ORDER BY [left] DESC

, , , , - FK.

:

delete from [group] where groupid in (
  select top 1000000 * from [group] 
  where groupid <> '00000000-0000-0000-0000-000000000000' 
  ORDER BY [left] desc
)

( ORDER BY, TOP), , DELETE , .

, ?

+5
5

, SQL Server , SQL Server 2005 2008 , . !

groupId Eager Spool delete. , , , . Assert, , .

script, , MPTT, :


USE     tempdb
;
IF      OBJECT_ID(N'dbo.Test', N'U')
        IS NOT NULL
        DROP TABLE dbo.Test
;
CREATE  TABLE dbo.Test
        (
        groupId     UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED,
        parentId    UNIQUEIDENTIFIER REFERENCES dbo.Test(groupId) NULL,
        name        NVARCHAR(50) NOT NULL,
        lft         INTEGER NOT NULL,
        rgt         INTEGER NOT NULL,
        )
;
CREATE  NONCLUSTERED INDEX nc1 ON dbo.Test (parentId)
;
INSERT  dbo.Test (groupId, parentId, name, lft, rgt)
VALUES  ({guid '00000000-0000-0000-0000-000000000000'}, NULL, N'Food', 1, 18),
        ({guid '99163693-B535-47A1-9F5B-E5F235CEB8E2'}, {guid '00000000-0000-0000-0000-000000000000'}, N'Fruit', 2, 11),
        ({guid '6A3FFED1-0230-4909-A7D4-DD9CBA674A1D'}, {guid '99163693-B535-47A1-9F5B-E5F235CEB8E2'}, N'Red', 3, 6),
        ({guid '2E7A25D8-8555-4FEC-A0D3-8B8E60501F6A'}, {guid '6A3FFED1-0230-4909-A7D4-DD9CBA674A1D'}, N'Cherry', 4, 5),
        ({guid '20613684-1081-4A42-AEAE-F21250D56703'}, {guid '99163693-B535-47A1-9F5B-E5F235CEB8E2'}, N'Yellow', 7, 10),
        ({guid 'B6D5A4AB-1A10-4069-842D-AAE09E73B3CD'}, {guid '20613684-1081-4A42-AEAE-F21250D56703'}, N'Banana', 8, 9),
        ({guid '174FF0C3-BAE0-4023-8819-EC89E080834C'}, {guid '00000000-0000-0000-0000-000000000000'}, N'Meat', 12, 17),
        ({guid '3CA5356E-343B-461C-8602-B9ECAEA1304D'}, {guid '174FF0C3-BAE0-4023-8819-EC89E080834C'}, N'Beef', 13, 14),
        ({guid '79664130-ACDF-48CE-A33D-7473153819E8'}, {guid '174FF0C3-BAE0-4023-8819-EC89E080834C'}, N'Pork', 15, 16)
;
DELETE  dbo.Test
WHERE   groupId > {guid '00000000-0000-0000-0000-000000000000'}
;
+5

Try

ALTER TABLE [group] NOCHECK CONSTRAINT ALL
go
DELETE FROM [group] WHERE [groupId] <> '00000000-0000-0000-0000-000000000000'
go
ALTER TABLE [group] CHECK CONSTRAINT ALL
go

, .

select * into #temptable from [group] WHERE [groupId] = '00000000-0000-0000-0000-000000000000'
go
truncate table [group]
go
select * into [group] from #temptable
go
drop table #temptable
go
+2

FK, , FK, ? Kinda , .

0

:

CHECK / NOCHECK CONSTRAINT
0

:

declare @idgroup guid;
set @idgroup = 'whatever';

WITH n AS
(
    SELECT groupId, parentId
        FROM Table WHERE groupId=@idgroup
    UNION ALL
    SELECT e.groupId, e.parentId
        FROM Table e JOIN n ON n.groupId=e.parentId
)
--Unlike some other systems, we don’t have to worry about the order the records are deleted, 
--since all SQL Server referential constraints are deferred till the end of the query.
DELETE FROM Table
WHERE groupId IN (SELECT groupId FROM n)

- FK Constraint

0

More articles:


All Articles