Question: How to Update Two Tables in One Statement?
Answer: Not Possible.
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)
16 Comments. Leave new
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.
Adam, I agree with you but trust me the question which still get asked are surprising.
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.
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.
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.
Thanks @bellons91 – That’s the precise reason of this blog..
Can’t we update using join?
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…
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
Nice Trick!
Still it’s not in single statement!
In one way, it is. I call such things are workaround and tricks.
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
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”
Hi Pinal,
Yeah, I agree sometimes we don’t have choices.
Thanks,
Srini
Thanks for the agreement.