删除子项，但仅在没有子项的地方删除父记录.Sql Server 2008 [英] Delete children ,but delete parent record only where there no children.Sql Server 2008

问题描述

想知道您能否帮我做一个简单的 SQL 查询.

Wondering if you could help me with a simple SQL query.

我包含了一个简单的脚本来创建 2 个表和 01 SP.用一些非常小的数据.有什么建议?谢谢

I have included a simple script to create the 2 tables and 01 SP. With some very small data. Any suggestions? Thanks

我有两张桌子

• 客户
• 客户项目

SQL 查询应该:

1. 根据CustomerStoreID从CustomerItem表中删除所有项目.
2. 也删除客户，但仅当客户不再有孩子时才删除

创建表格和一些数据的脚本:

Script to create tables and some data:

BEGIN TRANSACTION;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
   DROP TABLE [dbo].[Customer]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer]
   ([CustomerID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Surname] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_Customer] 
    PRIMARY KEY CLUSTERED ([CustomerID] ASC)
   ) ON [PRIMARY]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerItem]') AND type in (N'U'))
   DROP TABLE [dbo].[CustomerItem]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerItem]
   ([CustomerItemID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL,
    [CustomerStoreID] [int] NOT NULL,
    [CustomerItemDescription] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_CustomerItem] 
    PRIMARY KEY CLUSTERED([CustomerItemID] ASC)
   ) ON [PRIMARY]
GO

INSERT INTO [dbo].[Customer]([CustomerID], [Name], [Surname])
   SELECT 1, N'John ', N'Smith' UNION ALL
   SELECT 2, N'Mark', N'Bloggs' UNION ALL
   SELECT 3, N'Richard', N'Lay'

INSERT INTO [dbo].[CustomerItem]([CustomerItemID], [CustomerID], [CustomerStoreID], [CustomerItemDescription])
   SELECT 1, 1, 1, N'BookOne' UNION ALL
   SELECT 2, 1, 1, N'BookTwo' UNION ALL
   SELECT 3, 1, 2, N'BookThree'UNION ALL
   SELECT 4, 1, 2, N'BookFour' UNION ALL
   SELECT 5, 2, 2, N'BookFive'UNION ALL
   SELECT 6, 2, 2, N'BookSix' UNION ALL
   SELECT 7, 3, 3, N'BookSeven' UNION ALL
   SELECT 8, 3, 3, N'BookEight'
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE id = OBJECT_ID(N'[dbo].[DeleteCustomerAndItemsByStoreId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE [dbo].[DeleteCustomerAndItemsByStoreId]
GO

CREATE PROCEDURE DeleteCustomerAndItemsByStoreId
                   @CustomerStoreID INT
/*
   Delete all customerItems  based on @CustomerStoreID
   Delete the customer itself when he not longer has customerItems
*/
AS
   DELETE FROM CustomerItem 
   WHERE CustomerStoreID = @CustomerStoreID

   ---Loop through the customer table and  Delete Customer if no longer has children
   --???
   COMMIT

基本上在处理/删除最后一个customerItem时也会删除客户.

Basically when processing/deleting the last customerItem also delete the customer.

存储过程只有一个参数@CustomerStoreID

推荐答案

delete from Customer where CustomerID in
(select distinct CustomerID 
 from customer c left outer join CustomerItem i 
  on c.CustomerID = i.CustomerID
 where i.CustomerID is null)

这篇关于删除子项，但仅在没有子项的地方删除父记录.Sql Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！