question

Murali avatar image
Murali asked

Updating Two Tables using single query

DECLARE @d DATETIME,@B DATETIME SET @d = '08/25/2010' SET @b = '08/25/2010' UPDATE T0 SET T0.STATUS='R',T2.WH=T0.WH FROM T0 INNER JOIN T1 ON T0.CODE=T1.CODE INNER JoIN T2 ON T2.ENTRY = T0.EnTRY In the above query...i am updating columns from two tables ...is it possible ...i am getting an error as "Multi-part identifier could not be bound" Any help highly appreciatable...
t-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

any other way to update...not using two update statements...
0 Likes 0 ·
GPO avatar image
GPO answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think that's the trick, get both update statements into a transaction.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.