Delete children ,but delete parent record only where there no children.Sql Server 2008

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

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

I have 2 tables

• Customer
• CustomerItem

The SQL query should:

1. Delete all the items from CustomerItem table based on CustomerStoreID.
2. Delete the customer as well BUT only when the customer has no children anymore

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

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

Stored procedure will only have one parameter @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)

delete c
from [Customer] as c
    left join [CustomerItem] as ci
    on ci.[CustomerID] = c.[CustomerID]
where ci.[CustomerID] is null;

try this

delete Customer 
where not exists (select 1 
                  from CustomerItem 
         where  Customer.CustomerID = CustomerItem.CustomerID)

Basically your stored proc should look something like this:

CREATE PROCEDURE DeleteCustomerAndItemsByStoreId
                    @CustomerStoreID INT
AS
   DECLARE @CustomerIDs TABLE(CustomerID INT)

   -- do the first delete and output the deleted CustomerID's into a table variable
   DELETE CustomerItem 
   OUTPUT DELETED.CustomerID INTO @CustomerIDs(CustomerID)
   WHERE CustomerStoreID = @CustomerStoreID

   -- delete from the Customer table when no CustomerItems exist anymore for that CustomerID
   DELETE dbo.Customer
   OUTPUT DELETED.CustomerID 
   FROM @CustomerIDs c
   WHERE
      dbo.Customer.CustomerID = c.CustomerID
      AND NOT EXISTS(SELECT * FROM dbo.CustomerItem WHERE CustomerID = c.CustomerID)

That should do the two-step delete - first delete all CustomerItem rows for the CustomerStoreID defined, and then delete those customers from that set of customers affected by the first delete that don't have any more CustomerItem entries.

Update: when I run this:

SELECT * FROM customer

EXEC DeleteCustomerAndItemsByStoreId @CustomerStoreID = 2

SELECT * FROM customer

I get:

CustomerID  Name       Surname
   1        John        Smith
   2        Mark        Bloggs
   3        Richard     Lay

CustomerID     -- that from the "OUTPUT Deleted.CustomerID"
    2

CustomerID  Name       Surname
   1        John        Smith
   3        Richard     Lay

Customer no. 2 is gone - that's with your script, your data