How do I select data from 1 table and use those results to delete from multiple tables while keeping a count of all records deleted

I am trying to create a sql stored procedure to do the following.  Is this possible.

The condition is going to apply to the first table Table1. I am passing in the cutoff date (@date)


Select * FROM Table1 where (LST_Logon_IVR is null and Setup_DT < @Date) or LST_Logon_IVR < @Date

For every record we get from the where statement we need to delete from all 3 tables and also keep a count of all records deleted


Table1
      [USERID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [LOGONID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LOGONIVR] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FI_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SETUP_DT] [datetime] NULL,
      [STATUS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [USER_TYPE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DT_MODIFIED_ALIAS] [datetime] NULL,
      [WHO_MODIFIED_ALIAS] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LST_LOGON_WEB] [datetime] NULL,
      [LST_LOGON_IVR] [datetime] NULL


Table2
      [USERID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [SYSTEM] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PIN] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STATUS] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FI_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DT_MODIFIED_PIN] [datetime] NULL,
      [WHO_MODIFIED_PIN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Table3
      [User_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Date_Time] [datetime] NULL
juliemcnicholsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
This will delete the records where the userid matches the criteria.
By keeping it in one transaction, you can just take counts from before the deletes and subtract them from the after counts to get your net records deleted.

DECLARE @Table1Start int
DECLARE @Table2Start int
DECLARE @Table3Start int
DECLARE @Table1End int
DECLARE @Table2End int
DECLARE @Table3End int


BEGIN

SELECT @Table1Start = COUNT(*) FROM Table1
SELECT @Table2Start = COUNT(*) FROM Table2
SELECT @Table3Start = COUNT(*) FROM Table3

DELETE FROM Table2
WHERE User_ID IN (SELECT USERID FROM Table1 where (LST_Logon_IVR is null and Setup_DT < @Date) or
LST_Logon_IVR < @Date)

DELETE FROM Table3
WHERE User_ID IN (SELECT USERID FROM Table1 where (LST_Logon_IVR is null and Setup_DT < @Date) or LST_Logon_IVR < @Date)

DELETE FROM Table1
WHERE User_ID IN (SELECT USERID FROM Table1 where (LST_Logon_IVR is null and Setup_DT < @Date) or LST_Logon_IVR < @Date)


SELECT @Table1End = COUNT(*) FROM Table1
SELECT @Table2End = COUNT(*) FROM Table2
SELECT @Table3End = COUNT(*) FROM Table3

END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBAduck - Ben MillerPrincipal ConsultantCommented:
The code above will work except that you will want the counts of the records deleted, not what is left.

After each DELETE statement, you will do

SELECT @Table1RowsDeleted = @@ROWCOUNT

Then after the next
SELECT @Table2RowsDeleted = @@ROWCOUNT

Just make sure that you do the statement directly following the DELETE statements above.
0
UnifiedISCommented:
Actually, I suggested using subtraction to get the affected number of records.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

DBAduck - Ben MillerPrincipal ConsultantCommented:
I know, I was just saying that many other things can be happening to the tables during the deletes, etc. and if he wanted a count of how many were deleted, he could get them at the time of deletion instead of doing the count(*) on the table before and after.  It saves many cycles in a Database engine to just get the count when it happens.

Both work.
0
juliemcnicholsAuthor Commented:
Thanks so much, the delete is working.  I am still trying to get the count to work, it is returning 0 always.  I tried moving the count to after each delete.  Example below.  I assume I removed something I shouldn't.  


@Date DateTime

AS

DECLARE @IB_USERRowsDeleted as int
DECLARE @IB_User_LockoutRowsDeleted as int
DECLARE @IB_SECURITYRowsDeleted as int


BEGIN

DELETE FROM IB_User_Lockout
WHERE User_ID IN (SELECT USERID FROM IC_Admin.IB_USER where (LST_LOGON_IVR is null and Setup_DT < @Date) or
LST_Logon_IVR < @Date)

SELECT @IB_User_LockoutRowsDeleted = @@ROWCOUNT


DELETE FROM IC_Admin.IB_SECURITY
WHERE UserID IN (SELECT USERID FROM IC_Admin.IB_USER where (LST_LOGON_IVR is null and Setup_DT < @Date) or LST_Logon_IVR < @Date)

SELECT @IB_SECURITYRowsDeleted = @@ROWCOUNT


DELETE FROM IC_Admin.IB_USER
WHERE UserID IN (SELECT USERID FROM IC_Admin.IB_USER where (LST_LOGON_IVR is null and Setup_DT < @Date) or LST_Logon_IVR < @Date)

SELECT @IB_USERRowsDeleted = @@ROWCOUNT


END
0
UnifiedISCommented:
I would try it with the SELECT COUNT(*) and see if it works for you.  Get it to work right, then get it to work better...
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Now you just need to select them out in a result set.

SELECT @IB_User_LockoutRowsDeleted as LockoutRowsDeleted,
         @IB_SECURITYRowsDeleted as SecurityRowsDeleted,
         @IB_USERRowsDeleted as UserRowsDeleted

0
juliemcnicholsAuthor Commented:
Thank you very much, that worked.
0
juliemcnicholsAuthor Commented:
used a mix of both solutions to acheive the result needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.