How to: Delete rows from a table using a join on a second table
In my current project, we have a tables where the primary key consists of two values. Based on the result of a query, we needed to delete a few rows from that table. The query returned the two key values and we needed to join that information in the delete statement. It's not rocket science, but it did cost us some searching before we had a solution. To make sure I don't forget, this small blog shows how we did it.
First of all, when you need to delete information from one table based on information from a second, then this is the way to go:
DELETE t1
FROM MyTable1 AS t1
inner join MYTABLE2 t2
ON t1.KeyValue1= t2.KeyValue1 and t1.KeyValue2 = t2.KeyValue2
As you can see, you simply add the join and specify the key values for that join. However, we needed the result from a query. To do that, you can use the following solution:
DELETE t1
FROM MyTable1 AS t1
inner join (SELECT KeyValue1, KeyValue2 from MyTable2) t2
ON t1.KeyValue1 = t2.KeyValue1 and t1.KeyValue2 = t2.KeyValue2
I simplified the query, because the original query is a lot more complex than this one. Again, it's not difficult, but could be useful if you run into a similar situation