Delete based on composite primary key of another table
Hopefully this is an easy one, I'm just not finding the answer on Friday. I have two tables with the same columns. The tables each have a composite primary key of two columns. I want to delete from Table_A where the same value exists in Table_B. So, I am using Delete from Table_A where (select Column_A, Column_B from Table_A) = (select Column_A, Column_B from Table_B) That doesn't work. How should I structure this query?
Try this: DECLARE @Table_A TABLE ( col_a INT, col_b INT ); DECLARE @Table_B TABLE ( col_a INT, col_b INT ); INSERT INTO @Table_A SELECT 1 , 101 UNION ALL SELECT 2 , 102 UNION ALL SELECT 3 , 103 UNION ALL SELECT 4 , 104 UNION ALL SELECT 5 , 105 UNION ALL SELECT 6 , 106 UNION ALL SELECT 7 , 107; INSERT INTO @Table_B SELECT 1 , 101 UNION ALL SELECT 3 , 103 UNION ALL SELECT 5 , 105 UNION ALL SELECT 7 , 107; SELECT * FROM @Table_A; SELECT * FROM @Table_B; DELETE FROM @Table_A FROM @Table_A AS A INNER JOIN @Table_B AS B ON B.col_a = A.col_a AND B.col_b = A.col_b; SELECT * FROM @Table_A; SELECT * FROM @Table_B;