Sql-server – Delete from table where not in another table using two columns

sql serversql-server-2008

I am trying to get my head around this delete statement I am trying to accomplish. I understand how to delete from table where column not in ( subquery to table2)

I have two tables with multiple columns, and each table has two columns making up the primary keys. I think you have to do a join maybe? I would like something in the lines of the following:

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1_PK1 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

I am trying remove rows from tbl1. Does this look close to correct?

Edit to add:

tbl1
PK1, PK2, Col1

tbl2
PK1,PK2, DateStr

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1.PK1,tbl1.PK2 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

What is left after running sqlfiddle should be:

tbl1
('T', '222','asdf')

tbl2
('T', '222','9/26/2014')

Best Answer

I suspect you're after something like this:

DELETE FROM tbl1
FROM tbl1
    LEFT OUTER JOIN tbl2
        ON tbl1.PK1 = tbl2.PK1
        AND tbl1.PK2 = tbl2.PK2
        AND tbl2.DateStr >= GETDATE() - 365
WHERE tbl2.PK1 IS NULL
    AND tbl2.PK2 IS NULL

Yes, you have two FROM clauses with a multi-table DELETE statement. This is essentially just an anti-join turned into a deletion.

Related Question