How to update 2 tables with 1 statement in SQL Server..the hard way....

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