question

You can't update two tables in same statement, and I recommend you to use the MERGE statement if you are using SQL server 2008 to prevent issues caused by non-deterministic UPDATES (applies to all SQL server versions). [ http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx][1] > "The results of an UPDATE statement are > undefined if the statement includes a > FROM clause that is not specified in > such a way that only one value is > available for each column occurrence > that is updated (in other words, if > the UPDATE statement is not > deterministic). For example, given the > UPDATE statement in the following > script, both rows in table s meet the > qualifications of the FROM clause in > the UPDATE statement, but it is > undefined which row from s is used to > update the row in table t." CREATE TABLE s (ColA INT, ColB DECIMAL(10,3)) GO CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3)) GO INSERT INTO s VALUES(1, 10.0) INSERT INTO s VALUES(1, 20.0) INSERT INTO t VALUES(1, 0.0) GO UPDATE t SET t.ColB = t.ColB + s.ColB FROM t INNER JOIN s ON (t.ColA = s.ColA) GO [1]: http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

It would be useful to know a bit more about why you need to update two tables simultaneously. If you're worried about values changing between when the first table was updated and when the second table was updated, why not declare a variable to store the value in, so that the value stays constant between the two updates? Wrap both updates in a transaction so that if anything goes wrong you can roll both back and you won't end up with one table updated and not the other.