Post reply

How to delete records selected from another table in stored procedure

  • January 24, 2022 at 1:48 pm

    Go to Answer

    #3976523 

    CREATE PROCEDURE usp_DeleteEmployeeRcord
    (
     @days Int
    )
    AS 
            BEGIN
                DELETE [dbo].[EmpMainTable]  
                Where created < DATEADD(dd, -@days, GETDATE());
            END

    Hello Everyone,

    Above is my stored Procedure. I need to delete records in 'EmployLog' table selected from 'EmpMainTable'. here i need to delete older data more than 1 month (@days parameter).

    How i need to select old records from one table and delete from another table in a single stored procedure.

    I have attached my table and stored procedure.

     

    Thanks

    Attachments:
    You must be logged in to view attached files.

  • 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.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply