CODEWITHSUNDEEP
sql-server
JoJo
JoJo

Reputation: 4923

How can I UPDATE 2 table columns in SQL Server with 1 query?

UPDATE Subscription.dbo.RefillSubscriptionHeader AS H
INNER JOIN Subscription.dbo.RefillSubscriptionDetail AS D 
ON D.reSubHeaderId = H.reSubHeaderId 
SET H.isActive2 = '0', D.isActive = '0'
WHERE reItemID = '56'

In the above query I am trying to join 2 tables and update the IsActive and IsActive2 columns.

Right now when I do a SQL syntax check I get

Incorrect syntax near the keyword 'AS'.

If I cannot update 2 columns from 2 tables the best bet would be to update the H table.

Upvotes: 0

Views: 244

Answers (2)

vstrien
vstrien

Reputation: 2605

Because an UPDATE statement will only hit one table, your best bet would then be 

UPDATE H
 SET H.isActive2 = '0'
FROM Subscription.dbo.RefillSubscriptionHeader AS H
INNER JOIN Subscription.dbo.RefillSubscriptionDetail AS D 
ON D.reSubHeaderId = H.reSubHeaderId 
WHERE reItemID = '56'

Of course, you can put both updates in one TRANSACTION, and if you provide the right TRANSACTION ISOLATION LEVEL you can still ensure the "atomicity" of the operation

Upvotes: 1

Har
Har

Reputation: 5004

An update statement can only update one table/view. Nice try though!

Upvotes: 0

Related Questions