Update a column of one table with a column of another table

Question

I want to update one column of first table with another column of other table with this query:

I need to update the both columns at a same time.

update tblMTD 
set tblMTD.agedebt = OST.balance, OST.balance = '0'
from tblMTD 
inner join OST on tblMTD.sub = OST.sub

I get an error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OST.balance" could not be bound.

If you're updating `tblMTD`, you cannot do a `SET` on a column from a second table in the same statement. `UPDATE` only ever applies to **a single table**. If you need to update two tables, then you need two `UPDATE` statements — marc_s, Mar 27 '15 at 12:06
but i want to update both tables in a single query, is there any procedure to update two tables at a same time in a same query.? — Muhammad Tahir, Mar 27 '15 at 14:47

score 0 · Answer 1 · answered Mar 27 '15 at 11:18

0

Is this what you are trying to achieve?

UPDATE tblMTD 
SET tblMTD.agedebt = OST.balance 
FROM tblMTD INNER JOIN OST ON tblMTD.sub = OST.sub
WHERE OST.balance = '0'

Hope this helps.

answered Mar 27 '15 at 11:18

PKirby

i want to update both at a same time with some conditions, i tried to run this query in two segments as : (update tblMTD set tblMTD.agedebt = OST.balance, OST.balance = '0' from tblMTD inner join OST on tblMTD.sub = OST.sub) + (update tblMTD set OST.balance = '0' from tblMTD inner join OST on tblMTD.sub = OST.sub) – Muhammad Tahir Mar 27 '15 at 11:28
but my condition fulfills in fist query, other query fails the condition aand updated the wrong field – Muhammad Tahir Mar 27 '15 at 11:32
where condition is :WHERE CAST(tblMTD.date as date) = CAST((SELECT MAX(date) FROM tblMTD where agedebt IS NULL) as date) AND tblMTD.invoiceamount > OST.balance – Muhammad Tahir Mar 27 '15 at 11:32
@Muhammad, You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATE statements are treated atomically. You can also batch them to avoid a round trip. Please see : http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 – PKirby Mar 27 '15 at 11:38

1 Answers1