Delete from child table with two parents without cascade delete in sql server

I need your guidance. I create a script to delete from 3 tables separately. meaning when I created the script is to delete from table3 (child) then delete from 2 (child), then delete from table1(parent) I am having problem with Table3 since table2 has foreign key both for table2 and table1 so I cannot delete it before I delete table2 and if I delete table2 first then I lose reference that I use to find the rows to be delete then I delete 0 from table3 and table1.

The script looks like following

DELETE FROM table3 t3
        INNER JOIN table1 t1 ON t3.t1ID = t1.t1ID
        INNER JOIN table2 t2 i ON t2.t1ID = t1.t1ID
        WHERE t3.t1ID IN(
         SELECT DISTINCT t1.t1ID            
                    FROM t2     
                        WHERE (
                              (t2.t2ID IS NULL AND t1.LastDate <@UpDate ))) 

the above delete 0 since I delete table 2 before deleting from

DELETE 
        FROM table2 t2 
         INNER JOIN table1 t1 on t2.T1ID =t1.t1ID 
         WHERE t2.t1ID IN(
         SELECT DISTINCT t1.T1ID            
                    FROM t2                 
                        WHERE (
                              (t2.t2ID is null and t1.LastDate <@UpDate <@UpDate )))

It does not delete from table1 either since the script looks up where does not exist in table2

DELETE 
        FROM table1 t1
        INNER JOIN table2 t2 i ON t2.t1ID = t1.t1ID
        WHERE t1.t1ID IN(
         SELECT DISTINCT t1.t1ID        
                    FROM t2 
                        WHERE (
                              (t2.t2 IS NULL ANDt1.LastDate <@UpDate)))

Answer

Try something like this:

DECLARE @RowsToDelete TABLE
(
    RowsToDeleteID int IDENTITY(1,1) PRIMARY KEY,
    Table1ID int,
    Table2ID int,
    Table3ID int
);

INSERT @RowsToDelete (Table1ID, Table2ID, Table3ID)
SELECT DISTINCT t1.t1ID, t2.t2ID, t3.t3ID 
FROM table3 AS t3
INNER JOIN table1 AS t1 
ON t3.t1ID = t1.t1ID
INNER JOIN table2 AS t2 
ON t2.t1ID = t1.t1ID
WHERE t2.t2ID IS NULL 
AND t1.LastDate < @UpDate;

DELETE table2
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table2ID = t2ID);

DELETE table1
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table1ID = t1ID);

DELETE table3
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table3ID = t3ID);

Haven’t got the tables, etc. to try it but looks like it should be ok.

I’m also presuming here that those rows aren’t possibly changing while you’re doing this, otherwise you have potential race conditions.