Delete if all rows match condition

October 4, 2023

Table T1 has an ID column. Table T2 has a column with the ID column of T1 but it’s a 1:n mapping (ID of T1 can be in multiple rows of T2). It is NOT a foreign key.

Example data:

T1:
ID
42
43

T2:
FooID | Timestamp
42    | '2023-10-04T07:30:00'
42    | '2023-10-04T08:30:00'
43    | '2023-10-04T07:30:00'

I want to delete the row in T1 if ALL ROWS with the same ID in T2 match one condition.

DELETE foo 
FROM T1 foo
JOIN T2 bar ON foo.ID = bar.FooID
WHERE bar.TimeStamp <= '2023-10-04T08:00:00'

This would delete Id 42 and 43 because ONE row of T2 match the condition. I dont want to delete Id 42 in that case.

Is this possible?

Thank you.

>Solution :

You can do it with a EXISTS:

create table #t1 (
    id int
)

create table #t2 (
    fooID int
,   timestamp datetime
)

insert into #t1
values (1),(2),(3)

insert into #t2
values  (1, '20231001')
,   (1, '20231002')
,   (1, '20231003')
,   (3, '20231001')
,   (3, '20231002')
,   (3, '20231005')
,   (5, '20231001')
,   (5, '20231002')
,   (5, '20231005')

DELETE foo 
--output deleted.* -- expect id = 1 only
FROM #T1 foo
WHERE EXISTS(
        SELECT  1
        FROM    #T2 bar
        WHERE   foo.ID = bar.FooID
        HAVING COUNT(*) = COUNT(CASE WHEN bar.TimeStamp <= '2023-10-04T08:00:00' THEN 1 END)
        AND COUNT(*) > 0 -- Just in case no rows exist!
    )

This make sure that t2 has corresponding rows with t1 and also that total count of rows matches count of your condition.