Delete from multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Delete from multiple tables

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-08-27 : 09:02:51

Hi

How can I delete rows from multiple tables using a filter. I have set the l;ast day of the month as the first condition then want to lookup the where statement to include the And statement to remove records that match the surrogate keys.
Can I do a union all on the surrogate keys columns?

Thanks

DECLARE @MonthEnd datetime
SET @MonthEnd = EOMONTH(GETDATE())

IF @MonthEnd = GETDATE() -- Begin if date is last day of month..
BEGIN

DELETE DIM_INCIDENT2 --can I add multiple tables here to include all the tables where the surrogate keys match??
FROM DIM_INCIDENT2 i
JOIN dim_assignee a
on i.IncidentSK = a.AssigneeSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112))
AND i.IncidentSK = a.AssigneeSK

RETURN
END
ELSE
BEGIN
PRINT '*** Sorry This Code will Only Execute on Last Day of the Month'
END

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-08-27 : 10:14:33

Is it possible to have a foriegn key relationship for the surrogate key columns with delete cascade option

Like this?

ALTER TABLE Dim_Assignee
ADD CONSTRAINT fk_assignee
FOREIGN KEY (AssigneeSK)
REFERENCES Dim_Incident2(IncidentSK)
ON DELETE CASCADE;

SZ1
to learn is to show the universe that you care...!

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 11:11:19

Yes, the ON DELETE CASCADE will do the job, but I would caution you to only use that feature if the logical relationship exists and you ALWAYS want those rows to be linked. Additionally, you might find that the order of operations may conflict with your application logic and you create deadlock scenarios.

Another method is to use a transaction, SELECT or OUTPUT your Foreign key values into a table variable or temp table, DELETE from your tables in sequence, and then commit your transaction. Again, you will want to be careful about the sequence to avoid deadlocking scenarios.

Finally, I just want to point out this line in your code: IF @MonthEnd = GETDATE(). GETDATE() is a DATETIME with precision down to the ms whereas your @MonthEnd variable will be truncated to the day, so you will want to do the same to getdate() for the comparison:

IF @MonthEnd = CONVERT(DATE,GETDATE())

I would also define @MonthEnd as a DATE datetype to avoid an implicit conversion there too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 03:09:21

i would prefer doing this inline in a procedure rather than using ON DELETE CASCADE. That will make logic more clear and easier to maintain later. Otherwise if someone is unaware of full relationship chain it would be difficult to trace out which all tables will get affected by single delete operation. I would start from the child table first and then move on until I reach the parent table (in this case DimIncident).
you can use the date comparison condition and capture all surrogate keys for records affected by it in a temp table. Then using this temp table delete/modify all the records from child tables to remove dependency. Finally delete required records from your first dim table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-08-28 : 04:00:08

Thanks for the advice guys. Are we saying then I still need to apply the foreign keys to all child tables that include the surrogates to the Incident table as such:
ALTER TABLE Dim_Assignee
ADD CONSTRAINT fk_assignee
FOREIGN KEY (AssigneeSK)
REFERENCES Dim_Incident2(IncidentSK)
ON DELETE CASCADE;

Then Are we saying I need to start removing rows from the childs then the parent after. I've altered the @MonthEnd variable. Im just wondering the best way to do this now, could one of you give an example using the child table Dim_Assignee to remove the rows giving the first piece of code at the top? I can then add other tables to the script, leaving the Dim_Incident table to the end.

Tnanks

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-08-28 : 04:28:25

Could I not do this for every child table?

DECLARE @MonthEnd date
SET @MonthEnd = EOMONTH(GETDATE())

IF @MonthEnd = Convert(Date,GETDATE()) -- Begin if date is last day of month..
BEGIN

DELETE DIM_ASSIGNEE FROM DIM_ASSIGNEE a --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On a.AssigneeSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) --older than 10 days

RETURN
END
ELSE
BEGIN
PRINT '*** Sorry This Code will Only Execute on Last Day of the Month'
END

Go

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-08-28 : 04:57:15

This would work, at first I thought how can the child tables compare to the parent as the snapshotkey is only present in the parent and not the child tables, but the cross reference will take affeect from the JOIN SK fields and delete rows older than 10 days looked up from the INCIDENTSK table.

I tried to batch these one by one with a Go but that wont work as it would mean creating a seperate query for each DELETE.

What do you think? Im a way off base here...

/*Delete rows older than 10 days*/
Use IncidentDM_Testing
Go

DECLARE @MonthEnd date
SET @MonthEnd = EOMONTH(GETDATE())

IF @MonthEnd = Convert(Date,GETDATE()) -- Begin if date is last day of month..
BEGIN

DELETE DIM_ASSIGNEE FROM DIM_ASSIGNEE a --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On a.AssigneeSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

DELETE DIM_CLIENTNAME FROM DIM_CLIENTNAME c --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On c.ClientSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

DELETE DIM_CLIENTSITE FROM DIM_CLIENTSITE s --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On s.SiteSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

DELETE DIM_CUSTOMER FROM DIM_CUSTOMER cu --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On cu.CustomerSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last10 days

DELETE DIM_PHONE FROM DIM_PHONE p --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On p.PhoneSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

DELETE DIM_TASK FROM DIM_TASK t --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On t.TaskSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

DELETE DIM_TEAM FROM DIM_TEAM tm --does not contain snapshotkey
JOIN DIM_INCIDENT i --contains snapshotkey
On tm.TeamSK = i.IncidentSK
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

/*main parent table* ********PERFORM LAST********/
DELETE DIM_INCIDENT FROM DIM_INCIDENT i -- does contain snapshotkey
WHERE [SnapshotDateKey] < convert(int,convert(varchar(8), GetDate() -10, 112)) -- keep last 10 days

RETURN
END

ELSE
BEGIN
PRINT '*** Sorry This Code will Only Execute on Last Day of the Month'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources