Deleting With Joined Tables
In a recent blog, I discussed how to get output from multiple tables by using the join option in SQL. Joins can also be used to control deleting records.
The principle is the same, in that a table is used, and additional tables connected are joined to it. The syntax is a little different, however.
So, let us assume that we have TABLEA and TABLEB, with FIELD1 in both tables, that they can be joined by. The join syntax, to display all records where FIELD1 is the same in both tables, would be:
SELECT *
from TABLEA
inner join TABLEB on TABLEA.FIELD1=TABLEB.FIELD1
That should look familiar from my previous blog on the different types of joins in SQL.
Now, if we want to delete all records in TABLEA, where FIELD1 occurs in table B, the syntax is a little different than if you were to delete records from a table without a join. A straight forward delete like this has a syntax like:
DELETE from TABLEA where …
Simply, delete from the table, and control what is deleted by using the where clause.
When using a join to delete records, it is a little different in that the table that you want the records to be deleted from has to be specified, as well as the join. So, the syntax to delete the records from TABLEA that where FIELD1 occurs in TABLEB is:
DELETE TABLEA
from TABLEA
inner join TABLEB on TABLEA.FIELD1=TABLEB.FIELD1
The above statement will remove all the records in TABLEA, that are returned by the "from … inner join" clause. Additionally, the where clause can be used, for further control over exactly what records are removed.
I usually do a select statement (such as SELECT TABLEA.* …), to check that I am getting exactly the records that I want to remove. When I am satisfied that I have the correct join, and where clause if needed, then I simply change the "SELECT TABLEA.*" to "DELETE TABLEA", and delete the records.