patternsqlMinor
Delete from table where not in another table using two columns
Viewed 0 times
deletecolumnswheretwoanotherusingfromnottable
Problem
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:
I am trying remove rows from tbl1. Does this look close to correct?
Edit to add:
tbl1
PK1, PK2, Col1
tbl2
PK1,PK2, DateStr
What is left after running sqlfiddle should be:
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')Solution
I suspect you're after something like this:
Yes, you have two
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 NULLYes, you have two
FROM clauses with a multi-table DELETE statement. This is essentially just an anti-join turned into a deletion.Code Snippets
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 NULLContext
StackExchange Database Administrators Q#74022, answer score: 7
Revisions (0)
No revisions yet.