更新基于列的数据库表中的列在另一个表

Question

我有两个表

T1 
|id|balance| 
|1| 1000 | 
|2| 2000 | 
|3| 3000 | 

T2 
|id|rate|months| 
|1|0.50| 10 | 
|2|0.25| 24 | 
|3|0.40| 16 | 

我要更新表T1的余额列

T1.Balance = T1.Balance + T2.rate*T2.months*T1.Balance 

我该怎么做在MS SQL服务器？谢谢！

Answer 1

我创建临时表重建，所以你可以使用下面的更新：

CREATE TABLE #T1 
(id INT NOT NULL, 
balance money NOT NULL) 

INSERT INTO #T1 
(id,balance) 
    VALUES 
(1, 1000), 
(2, 2000) , 
(3, 3000) 


CREATE TABLE #T2 
(id INT NOT NULL, 
rate MONEY NOT NULL, 
months INT NOT NULL) 

    INSERT #T2 
(id,rate,months) 
    VALUES 
    (1,.5,10), 
    (2,.25,24), 
    (3,.4,16) 

    --confirm data 
    SELECT * FROM #T1 
    SELECT * FROM #T2 

    --update balances 
    update t1 SET balance = T1.Balance + (T2.rate*T2.months*T1.Balance) 
    FROM #T1 AS t1 
    JOIN #T2 AS t2 
     ON t1.id = t2.id 

Answer 2

下面的查询可以帮助你更新所有的行，使用单个查询特定列。

UPDATE T1 AS t1 SET t1.balance = t1.balance * (SELECT t2.rate * t2.months FROM T2 AS t2 WHERE t2.id = t1.id); 

或者你也可以试试这个

UPDATE T1 AS t SET t.balance = (t1.balance * t2.rate * t2.months) FROM T1 AS t1 JOIN T2 AS t2 ON t1.id = t2.id;