question

siugoalie78 avatar image
siugoalie78 asked

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?
sql-server-2008-r2tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
DenisT avatar image
DenisT answered
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;
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siugoalie78 avatar image siugoalie78 commented ·
Thanks very much! Exactly what i was looking for!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.