asked
Delete from table B based on Table A
MS SQL Server 2012 Table A ID Date 1 2013-11-01 2 2014-02-15 (Many more ID/Date combinations. ID is unique) Table B (Neither ID nor Date nor the combination is unique.) ID Date 1 2013-10-20 1 2013-10-30 1 2013-11-01 1 2013-11-10 (many more for ID = 1) 2 2014-02-14 2 2014-02-15 2 2014-02-16 (Many more for ID = 2) 4 2014-02-02 4 2014-02-03 4 2014-02-03 For each row in Table A, I want to delete all the entries in Table B where
TableA.ID =
TableB.ID and TableB.Date >= TableA.Date This is a bit simplified version of what I am doing but I believe it should be enough to get an answer. At least I hope so. For reference, Table A has 40 entries. Table B has over 50 million of which 8 million will match Table A's ID and be >= the corresponding date. I have read all the caveats about For Each and Cursors and would love to learn how to do this the "proper" way.
Well, here's one way of doing it using a single `DELETE` statement: -- Just setting up a couple of table variables to show the concept DECLARE @table_a AS TABLE ( id INT, d DATE ) DECLARE @table_b AS TABLE ( id INT, d DATE ) -- and loading those table variables with data to match what you've supplied INSERT INTO @table_a VALUES ( 1, '2013-11-01' ), ( 2, '2014-02-15' ) INSERT INTO @table_b VALUES ( 1, '2013-10-20' ), ( 1, '2013-10-30' ), ( 1, '2013-11-01' ), ( 1, '2013-11-10' ), ( 2, '2014-02-14' ), ( 2, '2014-02-15' ), ( 2, '2014-02-16' ), ( 4, '2014-02-02' ), ( 4, '2014-02-03' ), ( 4, '2014-02-03' ) -- Just checking... SELECT * FROM @table_a SELECT * FROM @table_b -- Here's the delete: DELETE FROM @table_b FROM @table_b AS b INNER JOIN @table_a a ON
b.id =
a.id AND b.d >= a.d -- And here's the output: SELECT * FROM @table_b This is based on Sample C in the [MSDN documentation for `DELETE`][1]. [1]:
http://msdn.microsoft.com/en-gb/library/ms189835.aspx
16 People are following this question.
