Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Deleting multiple linked records

    Hi guys, how is it possible using the t-sql DELETE statement to delete multiple records linked to each other?

    Let me explain the scenario as simply as i can.

    I have 3 tables, Table A, Table B, and Table C.

    Each entry in table A is linked to multiple records in Table B, and then each record in Table B is linked to one record in Table C.

    I know how to delete the linked records from Table A and Table B, but how do i delete the records linked between Table B and Table C?

    I know how i would do it in c#, something like this:

    foreach (entry A in Table b)
    {
    int a = Table B.linkId //The attribute that links Table B and Table C
    delete entry in table B;
    delete entry in Table C where linkId = a;
    }

    Or something like that...

    Thanks!

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    If you with "linking" mean foreign keys, these can be configured with cascading. If so, you can delete one row and SQL Server does the rest for you. It should be used with caution though, it is possible to clear a whole database this way.

    Apart from this, you could create a stored procedure doing it for you.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by roac View Post
    If you with "linking" mean foreign keys, these can be configured with cascading. If so, you can delete one row and SQL Server does the rest for you. It should be used with caution though, it is possible to clear a whole database this way.
    Interpret the red text as: NOOOO!!! NEVER EVER use CASCADE!!!!!!! CASCADE works perfect in a perfect world. If you live in a perfect world, you can use CASCADE safely, otherwise don't use it.

    You could write something in the line of:
    Code:
    DELETE TableC
    FROM TableC
    	INNER JOIN TableB ON
    		TableC.TableB_Id = TableB.Id
    	INNER JOIN TableA ON
    		TableB.TableA_Id = TableA.Id
    WHERE TableA.someColumn = 'SomeValue'
    
    DELETE TableB
    FROM TableB
    	INNER JOIN TableA ON
    		TableB.TableA_Id = TableA.Id
    WHERE TableA.someColumn = 'SomeValue'
    
    DELETE 
    FROM TableA
    WHERE TableA.someColumn = 'SomeValue'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2011
    Posts
    63
    Wait you mean i can use joins to get the full record from the 3 seperate tables, and then use delete to delete them all?

    I figures scince ALTER TABLE only works on one table at a time that DELETE was like that too.

  5. #5
    Join Date
    Jun 2011
    Posts
    63
    Ok i tried it like this:

    Delete *
    FROM Maps INNER JOIN
    mapGrids ON Maps.mapId = mapGrids.mapId INNER JOIN
    Links ON mapGrids.linkId = Links.linkId INNER JOIN
    linkTo ON Links.linksToId = linkTo.linkToId
    WHERE maps.mapId = @mapId

    But it gives me a compiler error near *.

  6. #6
    Join Date
    Jun 2011
    Posts
    63
    Nm, got it to work. For reference, here is what i did:

    Code:
    USE [dbTalesOfEpic]
    GO
    /****** Object:  StoredProcedure [dbo].[deleteMap]    Script Date: 08/09/2011 21:05:50 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[deleteMap]
    
    @status tinyint output,
    @mapId int
    
    AS
    
    BEGIN TRY
    
    BEGIN TRANSACTION
    
    SELECT Links.linkToId 
    INTO #tempLinks
    FROM Maps INNER JOIN
                          mapGrids ON Maps.mapId = mapGrids.mapId INNER JOIN
                          Links ON mapGrids.linkId = Links.linkId INNER JOIN
                          linkTo ON Links.linkToId = linkTo.linkToId
    WHERE maps.mapId = @mapId
    
    SELECT mapGrids.linkId
    INTO #tempMapGrids
    FROM Maps INNER JOIN
                          mapGrids ON Maps.mapId = mapGrids.mapId INNER JOIN
                          Links ON mapGrids.linkId = Links.linkId INNER JOIN
                          linkTo ON Links.linkToId = linkTo.linkToId
    WHERE maps.mapId = @mapId
    
    DELETE mapGrids
    FROM Maps INNER JOIN
                          mapGrids ON Maps.mapId = mapGrids.mapId
    WHERE maps.mapId = @mapId
    
    DELETE Links
    FROM #tempMapGrids INNER JOIN Links ON #tempMapGrids.linkId = Links.linkId
    
    Delete linkTo
    FROM #tempLinks INNER JOIN LinkTo ON #tempLinks.linkToId = LinkTo.linkToId
    
    DELETE Maps
    FROM Maps
    WHERE maps.mapId = @mapId
    
    SET @status = 1	
    	
    COMMIT
    
    END TRY
    BEGIN CATCH	
    
    	SET @status = 0
    
    IF @@TRANCOUNT>0
    	ROLLBACK
    
    END CATCH
    
    RETURN @status

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •