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 -

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.

As i think so that it is impossible to update or delete same table in a single query but it is possible to insert or update multiple table in single query using merge statement correct me if I am wrong.

Here is my question,I have 200 tables ,some tables have one of the column name as ‘servername’,servername has 2000 rows of data.I want to update the data with other data in all the tables which is having the column has as servername.,how can we do this.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.