How can I delete using INNER JOIN with SQL Server?
This code example uses SQL Server 2014 Standard
I'm always nervous about deleting records from a database in case I make a very expensive mistake, so in this blog, I will show you how to achieve this using the delete statement. The tables I'm using can be found in a previous post here.
To start let's add a new row to the PersonNameAndAddress table, the code for this is below:
INSERT INTO PersonNameAndAddress (FirstName, LastName, CityName,PostCode, Country) VALUES (N'Tom', N'Jones', N'Cardiff', N'CF', N'Wales')
If we now check the table, we can see the new row has been added.So Tom Jones has now been added to the table, but we now have to delete all persons from Cardiff, so how do we achieve this.
First of we run a sanity check and use a select statement to ensure what we are doing is correct, the code for that is below:
--DELETE a select a.* FROM [dbo].[PersonNameAndAddress] a INNER JOIN [dbo].[PersonAddress] b ON a.PersonId= b.PersonId WHERE b.PersonId= 13
Notice how at the moment I have commented out the DELETE, run the above code and you should see the following:So we now know that our SQL select statement is returning the correct results, so now if we run the delete statement, we will see that Tom Jones has in fact been deleted.
Let's change things around and in the PersonAddress table, make the CityName the primary key and then we can do a join and make the CityName in the PersonNameAndAddress the foreign key, also ensure that we have cascade update and delete set as per the image below:
Now if we run a delete statement on the PersonAddress table where CityName is Cardiff, it will delete all rows in the PersonNameAndAddress where CityName is Cardiff as the image below shows.But if we remove the Cascade delete and try again, we will get the following error:
Msg 547, Level 16, State 0, Line 19
The DELETE statement conflicted with the REFERENCE constraint "FK_PersonNameAndAddress_PersonAddress". The conflict occurred in database "SQLDemo", table "dbo.PersonNameAndAddress", column 'CityName'.
The statement has been terminated.
The above is something to watch out for when deleting rows from one table that has a join on another table and cascade delete is not turned on.
Hope you found this blog helpful, thanks for reading