juliemcnichols
asked on
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_
[LOGONID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[LOGONIVR] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[FI_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[SETUP_DT] [datetime] NULL,
[STATUS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[USER_TYPE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_
[DT_MODIFIED_ALIAS] [datetime] NULL,
[WHO_MODIFIED_ALIAS] [char](15) COLLATE SQL_Latin1_General_CP1_CI_
[LST_LOGON_WEB] [datetime] NULL,
[LST_LOGON_IVR] [datetime] NULL
Table2
[USERID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[SYSTEM] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[PIN] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_
[STATUS] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_
[FI_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[DT_MODIFIED_PIN] [datetime] NULL,
[WHO_MODIFIED_PIN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
Table3
[User_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[Date_Time] [datetime] NULL
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_
[LOGONID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[LOGONIVR] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[FI_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[SETUP_DT] [datetime] NULL,
[STATUS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[USER_TYPE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_
[DT_MODIFIED_ALIAS] [datetime] NULL,
[WHO_MODIFIED_ALIAS] [char](15) COLLATE SQL_Latin1_General_CP1_CI_
[LST_LOGON_WEB] [datetime] NULL,
[LST_LOGON_IVR] [datetime] NULL
Table2
[USERID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[SYSTEM] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[PIN] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_
[STATUS] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_
[FI_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[DT_MODIFIED_PIN] [datetime] NULL,
[WHO_MODIFIED_PIN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
Table3
[User_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[Date_Time] [datetime] NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually, I suggested using subtraction to get the affected number of records.
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.
Both work.
ASKER
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_LockoutRowsDelete
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_LockoutRowsDelete
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
@Date DateTime
AS
DECLARE @IB_USERRowsDeleted as int
DECLARE @IB_User_LockoutRowsDelete
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_LockoutRowsDelete
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
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much, that worked.
ASKER
used a mix of both solutions to acheive the result needed.
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.