There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them.
Let us see the questions here.
I want to delete from multiple table in a single statement how will I do it?
I want to update multiple table in a single statement how will I do it?
The answer is – No, You cannot and you should not.
SQL Server does not support deleting or updating from two tables in a single update. If you want to delete or update two different tables – you may want to write two different delete or update statements for it. This method has many issues – from the consistency of the data to SQL syntax.
Now here is the real reason for this blog post – yesterday I was asked this question again and I replied my canned answer saying it is not possible and it should not be any way implemented that day. In the response to my reply I was pointed out to my own blog post where user suggested that I had previously mentioned this is possible and with demo example. Let us go over my conversation – you may find it interesting. Let us call the user DJ.
DJ: Pinal, can we delete multiple table in a single statement or with single delete statement?
Pinal: No, you cannot and you should not.
DJ: Oh okey, if that is the case, why do you suggest to do that?
Pinal: (baffled) I am not suggesting that. I am rather suggesting that it is not possible and it should not be possible.
DJ: Hmm… but in that case why did you blog about it earlier?
Pinal: (What?) No, I did not. I am pretty confident.
DJ: Well, I am confident as well. You did.
Pinal: In that case, it is my word against your word. Isn’t it?
DJ: I have proof. Do you want to see it that you suggest it is possible?
Pinal: Yes, I will be delighted too.
(After 10 Minutes)
DJ: Here are not one but two of your blog posts which talks about it -
SQL SERVER – Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – Part 1 of 2
SQL SERVER – Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2
Pinal: Oh!
DJ: I know I was correct.
Pinal: Well, oh man, I did not mean there what you mean here.
DJ: I did not understand can you explain it further.
Pinal: Here we go.
The example in the other blog is the example of the cascading delete or cascading update. I think you may want to understand the concept of the foreign keys and cascading update/delete. The concept of cascading exists to maintain data integrity. If there primary keys get deleted the update or delete reflects on the foreign key table to maintain the key integrity and data consistency. SQL Server follows ANSI Entry SQL with regard to referential integrity between PrimaryKey and ForeignKey columns which requires the inserting, updating, and deleting of data in related tables to be restricted to values that preserve the integrity. This is all together different concept than deleting multiple values in a single statement.
When I hear that someone wants to delete or update multiple table in a single statement what I assume is something very similar to following.
DELETE/UPDATE Table 1 (cols) Table 2 (cols)
VALUES … which is not valid statement/syntax as well it is not ASNI standards as well.
I guess, after this discussion with DJ, I realize I need to do a blog post so I can add the link to this blog post in my canned answer. Well, it was a fun conversation with DJ and I hope it the message is very clear now.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Joins, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
