mysql - 根据另一个表中的列更新数据库表中的列

Question

我有两张 table

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 Server 中执行此操作？谢谢!

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