Every now and then you will get someone asking how to update two tables with one statement in SQL Server. The answer is usually, no that is not possible... the person then walks away muttering something about how MySQL allows it.
So I decided to try to see if I could update two tables with one statement. I decided to try a couple of different things
- view
- common table expression
- indexed view
- instead of trigger
In order to begin we need two tables, each table will have one row of data so we can update those rows
CREATE TABLE test1(id int primary key, someVal char(1) not null) CREATE TABLE test2(id int primary key, someVal char(1) not null) INSERT test1 VALUES(1,'a') INSERT test2 VALUES(1,'a') Go
Now we can start with plan A... the mighty view
CREATE VIEW Testview1 AS SELECT t1.*,t2.SomeVal as SomeVal2 FROM test1 t1 JOIN test2 t2 on t1.id = t2.id
Running a simple select against the view
SELECT * FROM testview1
id someVal SomeVal2
1 a a
Time to update the view
UPDATE testview1 SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
Msg 4405, Level 16, State 1, Line 1
View or function 'testview1' is not updatable because the modification affects multiple base tables.
As you can see that didn't work since even though you are updating one view, you are still trying to update two tables.
Time to implement plan B... the versatile common table expression
Since you can update a common table expression, can you update a common table expression if it updates more than one table? Let's try it out
;WITH cte AS (SELECT t1.*,t2.SomeVal as SomeVal2 FROM test1 t1 JOIN test2 t2 on t1.id = t2.id) UPDATE cte SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
Msg 4405, Level 16, State 1, Line 1
View or function 'cte' is not updatable because the modification affects multiple base tables.
So plan B ended like plan A... also pretty much the same error message
Plan C.... the mighty indexed view.
If you ever tried working with indexed views you are probably busy cursing at the moment.
Let's create this view and add an index
CREATE VIEW testviewIndexed WITH SCHEMABINDING AS SELECT t1.id, t1.someVal, t2.SomeVal as SomeVal2 FROM dbo.test1 t1 JOIN dbo.test2 t2 on t1.id = t2.id GO CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON testviewIndexed (id);
Fingers crossed....but if the regular view didn't work..why would this?
UPDATE testviewIndexed SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
Msg 4405, Level 16, State 1, Line 2
View or function 'testviewIndexed' is not updatable because the modification affects multiple base tables.
That is right... plan C is also bad, very bad.
So we are left with one more thing... plan D (as in Denis)....
We will now use an instead of trigger on the regular view from before
CREATE TRIGGER InsteadTrigger on testview1 INSTEAD OF UPDATE AS BEGIN UPDATE t SET t.SomeVal = i.SomeVal FROM INSERTED i JOIN test1 t on i.id = t.id UPDATE t SET t.SomeVal = i.SomeVal2 FROM INSERTED i JOIN test2 t on i.id = t.id END GO
Let's see what happens now.. fingers crossed
UPDATE testview1 SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
No error, let's see what is in the table
SELECT * FROM test1 SELECT * FROM test2
id someVal SomeVal2
1 b b
And as you can see, you can update two tables with one statement.
Should you really go through all this trouble because you don't want to do something like this?
BEGIN TRAN UPDATE test1 SET SomeVal = 'c' WHERE id = 1 UPDATE test2 SET SomeVal = 'c' WHERE id = 1 COMMIT-- hmm where is the error checking/begin try xact_state()?
Nope I would not go this route, if the table changes you now need to also update the trigger. What if someone drops the trigger? There are too many ways this can go wrong
