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 (https://blog.sqlauthority.com)
13 Comments. Leave new
Good Read.
Absolutely,After I take a first look of this post, I thought of We can achieve this by using CASE statement Or using MERGE. After I read complete post I understood what DJ intention.
Hi Pinal,
Excellent post!
As i can see the complete chat, you explained this difference to DJ in pretty good way.
While it is still not possible to UPDATE or DELETE, you can atleast INSERT into multiple tables in a single statement.
For eg,
CREATE TABLE T1(id int, data int)
CREATE TABLE T2(id int, data int)
INSERT INTO T1 VALUES(1,1)
INSERT INTO t2 VALUES (1,1)
SELECT * FROM t1 inner join t2 on t1.id=t2.id
INSERT INTO T1
SELECT D.id, D.data
FROM (
INSERT T2
OUTPUT inserted.id, inserted.data
VALUES(2,2)
) D
SELECT * FROM t1 inner join t2 on t1.id=t2.id
this feature is called coposable DML and you can read more about it here
Regards
Roji Thomas
Good Read
Can we do Multiple Updates( updating different rows) on Single table at a time?
how to delete multiple table data in single sql query
thanks
Thanks but I need more reading to understand in. anyway Afterall I noknow it now. Thank you sir..
Sir,
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.
you need to write update statement which would have join with other tables.