February 11, 2013 at 12:30 pm
I am trying to automate purging data from two information tables. Table2 depends on Table1. Both tables have timestamp columns. I can remove the last 6 months of data from Table2, however when I try for Table1 I get a constraint issue.
Table2 has multiple rows associated to one row in Table 1. These multiple rows in Table2 can have timestamps that do not fall within the 6 month criteria. For example, Table1 can have a timestamp of 2012-05-31 12:35:56.963 and Table2 can have a timestamp of 2012-09-08 12:13:22.954. So when I try to delete from Table1 I get a constraint issue. I know I don't want to delete this record since it would create an orphaned record in Table2.
Is there a way for me to setup my DELETE statement so I will go to the next row when it hits a constraint issue? Thereby leaving that row, but allowing me to delete the other rows that do not have a constraint issue.
February 11, 2013 at 1:08 pm
You would need to code the time constraints in your WHERE clause. That is, bypass those rows yourself rather than trying to have SQL do it itself.
You might not necessarily have to hard-code the date for the WHERE: you could instead, for example, read the releveant CHECK constraint and pull the date value from there.
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".
February 15, 2013 at 3:14 pm
I tried this but same constraint issue.
Delete From T1
From database.dbo.Table1 T1
Where DateAdd(day, -180, GetDate()) > T1.Table1Date
And Not Exists(Select * From database.dbo.Table2 T2
Where T1.ID = T2.ID And T1.Type = T2.Type);
February 15, 2013 at 3:32 pm
This perhaps?
-- Delete from Table2 must run first
DELETE FROM
dbo.Table2 t2
WHERE
t2.TimeStampCol < @CutOffDateTime;
-- Then run delete from Table2
DELETE FROM
dbo.Table1 t1
WHERE
t1.TimeStampCol < @CutOffDateTime and
NOT EXISTS(SELECT 1 FROM dbo.Table2 t2 WHERE t2.JoinCol = t1.JoinCol);
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply