SQL SERVER – Not Possible – Delete From Multiple Table – Update Multiple Table in Single Statement

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

12 thoughts on “SQL SERVER – Not Possible – Delete From Multiple Table – Update Multiple Table in Single Statement

  1. 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.

    Like

  2. 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

    http://www.sqlmag.com/article/sql-server/composable-dml

    Regards
    Roji Thomas

    http://sqlindian.com

    Like

      • Ofcourse you can. I think we should collaborate more :)

        Note that you can use an INSERT INSERT, UPPDATE INSERT, DELETE INSERT or MERGE INSERT using composable DML.

        Also excellent picture selection there. That explains the concept pretty well there. I can’t think of a better image to explain composable DML. A picture is worth a thousand words, indeed.

        Regards
        Roji Thomas

        http://sqlindian.com

        Like

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  4. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s