SQL SERVER – How to Update Two Tables in One Statement?

Question: How to Update Two Tables in One Statement?

Answer: Not Possible.

SQL SERVER - How to Update Two Tables in One Statement? updatetables

There are some questions where the answer is no and that is totally fine. I often get asked that as it is possible to select  two or more tables in a single SELECT statement, is it possible to UPDATE more than one table in a single table. Well, the question is very valid, but it is truly not possible to do so.

Most of the time when I ask more details about the need of this particular need, the usual answer I get is that the user wants to perform two or more updates together in such a way that when success they both gets committed together or fails together.

There is another way this one feature can be achieved is via transactions.

You can wrap your around your more than one UPDATE statement in a single transaction, this way if one or more statement fails, the entire transaction rolls back.

Here is the sample example of how the transaction should work with multiple update statements.

BEGIN TRANSACTIONS;

UPDATE TABLE1
SET Col1 = 'SomeValue'
WHERE Cond1 = 'SomeCond';

UPDATE TABLE2
SET Col2 = 'SomeValue'
WHERE Cond2 = 'SomeCond';

COMMIT;

Let me know if this solution satisfies the need of updating two tables in a single statement. If not, I would love to know the reason, you want to update two more tables in a single statement.

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

, ,
Previous Post
SQL SERVER – Alternate to AGENT_DATETIME Function
Next Post
SQL SERVER – How to Measure Page Splits Counter Value via T-SQL?

Related Posts

16 Comments. Leave new

  • Adam Cruickshank
    June 7, 2017 1:02 am

    I’m stunned that this needs discussing in 2017.
    There’s probably an article from 20 years covering this.
    Anybody asking such a question needs to read a manual and feel lucky they exist.
    I used to learn without manuals and before Google was invented.

    Reply
    • Adam, I agree with you but trust me the question which still get asked are surprising.

      Reply
    • Adam, regarding your statement ‘read a manual and feel lucky they exist’; so, you came right out of the womb knowing everything, or perhaps you learn by osmosis? Amazing. That would lead one to wonder why you are reading this in the first place. Furthermore, who made you the judge as to the value of the existence of another? Go read some manuals or Google how not to be so judgemental and miserable.

      Reply
    • Fact is, as time progresses, technological advancements are made.
      Are you still coding with the same techniques/restrictions you had 20 years ago? If you are, then that would be something to be stunned at.

      Reply
  • Of course you are right, but I think this is due to the fact that most of the IT professionals are not focused only to a single technology like SQL but are full stack.
    In my (well, by now, short) experience, I had to learn both client side and server side technology, all on demand. If a frontend developer needs to create (or modify) a functionality in SQL, he has no time to read manuals, but only to have a glimpse on the topics related to the new functionality.

    Reply
  • Can’t we update using join?

    Reply
    • You can use update with join, but you can only update one of the joint tables.
      Update a
      set a.value1 = b.value2
      from a join b on…

      but not (or similar):
      Update a, b
      set a.value1 = b.value2, b.value5 = ‘x’
      from a join b on…

      Reply
  • Alexandr Pechenko
    January 2, 2018 8:43 pm

    you can use view with INSTEAD OF trigger:

    CREATE TABLE [dbo].[t1]( [id] [uniqueidentifier] NOT NULL, [FirstName] [varchar](50) NULL)
    go
    CREATE TABLE [dbo].[t2]( [id] [uniqueidentifier] NOT NULL, [LastName] [varchar](50) NULL)
    GO

    insert into t1
    values(’76CC1607-1DAE-4878-A549-26518696CEF8′,’Alexandr’)

    insert into t2
    values(’76CC1607-1DAE-4878-A549-26518696CEF8′,’Pechenko’)

    go

    create VIEW dbo.v1 with schemabinding
    as
    select dbo.t1.id, dbo.t1.FirstName, dbo.t2.LastName
    from dbo.t1
    join dbo.t2 on dbo.t2.id = dbo.t1.id

    go

    CREATE TRIGGER tr_v1_upd
    ON dbo.v1
    INSTEAD OF UPDATE
    AS

    UPDATE s
    SET s.FirstName = i.FirstName
    FROM dbo.t1 s
    JOIN inserted i
    ON s.id = i.id;

    UPDATE s
    SET s.LastName = i.LastName
    FROM dbo.t2 s
    JOIN inserted i
    ON s.id = i.id;

    go

    update dbo.v1 set
    FirstName = ‘FN’,
    LastName = ‘LN’
    where id = ’76CC1607-1DAE-4878-A549-26518696CEF8′

    go

    select * from v1

    Reply
  • Hi Pinal,

    It might be workaround, but why we do need to complicate it by creating view and trigger to achieve this? The same can be achieved using transactions easily.

    Thanks,
    Srinivas

    Reply
    • You are absolutely right. Sometimes things are not as simple as they sound in implementation. Will you be able to ask question to your manager if he says – “If SQL have some way, do it”

      Reply
  • Hi Pinal,

    Yeah, I agree sometimes we don’t have choices.

    Thanks,
    Srini

    Reply

Leave a Reply

Menu