How to delete records selected from another table in stored procedure

• DesNorton

  SSC-Insane

  Points: 24619

  More actions

  January 24, 2022 at 2:09 pm

  #3976526

  You will have to do 2 separate deletes.  And then I would also look at adding error handling and transaction control.

  Also, depending on the size of the data, you may want to do the deletes in batches.

  BEGIN
    /*** First delete the log records ***/  DELETE lg
    FROM [dbo].[EmpMainTable] AS mt
    INNER JOIN [dbo].[EmpLoyLog] AS lg ON mt.[Setld] = lg.[Setld]
    WHERE mt.created < DATEADD(dd, -@days, GETDATE());
  
    /*** Then delete the main records ***/  DELETE mt
    FROM [dbo].[EmpMainTable] AS mt
    INNER JOIN [dbo].[EmpLoyLog] AS lg ON mt.[Setld] = lg.[Setld]
    WHERE mt.created < DATEADD(dd, -@days, GETDATE());
  END

  ---

  How to post data/code on a forum to get the best help.

• ScottPletcher

  SSC Guru

  Points: 101207

  More actions

  January 24, 2022 at 3:46 pm

  Answer

  #3976576

  In SQL Server, you can use DELETE from a table using a JOIN.  Just be sure to alias the table you're deleting from and DELETE from the alias, not the original table name.  That is:

  DELETE FROM EMT

  FROM dbo.EmpMainTable EMT

  INNER JOIN ... ON ...

  [WHERE ...]

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".