Go to next record when there is a constraint issue.

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

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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);

  • 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 4 (of 4 total)

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