How to update multiple tables at the same time?

问题:

I'm trying to update fields from three different tables but I'm getting some errors:

UPDATE 
  a, b, c
SET 
  a.Locked = 0, 
  b.Locked = 0, 
  c.Locked = 0, 
  a.LockedByUsername = 'zolomon', 
  b.LockedByUsername = 'zolomon', 
  c.LockedByUsername = 'zolomon', 
  a.LockedAt = CURRENT_TIMESTAMP, 
  b.LockedAt = CURRENT_TIMESTAMP, 
  c.LockedAt = CURRENT_TIMESTAMP
FROM 
  TableA AS a
  INNER JOIN TableB as b ON n.Objid = o.Objid
  INNER JOIN TableC as c ON n.Namnid = e.Namnid 
WHERE
  a.Namn1 = 'FirstName LastName' AND b.objektkod='SomeIdentifier'

And the errors:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

回答1:

You can't update fields from multiple tables on a single update query. There error you are getting is because this is not permitted:

update a, b, c

Since you can only update one table per update command.

回答2:

As pointed out by other answers, in SQL an UPDATE updates only one table. That's normally enough for most practical needs. If you want to update several tables at the same time, you can just put the updates inside a transaction, and the effect will normally be the same.

If you are concerned about getting different timestamps (for your field lockedAt) first look into your database docs to check if your CURRENT_TIMESTAMP function refers to the start time of the transaction (e.g. PostgreSQL).

回答3:

you cannot update multiple tables in one statement. an option would be to use a stored procedure

回答4:

We can update it with join like this

UPDATE table1 
INNER join table2 on table1.id=table2.tab1_id
INNER join table3 on table1.id=table3.tab1_id
SET table1.status=1,table2.status=1,table3.status=1,table1.name='Premjith'
WHERE table1.id=1