mysql - 使用两个表中的数学运算更新字段

Question

我有两个带有两个数字字段的表。

mysql> desc cc_call;
+-----------------------+---------------+------+-----+---------------------+----------------+
| Field                 | Type          | Null | Key | Default             | Extra          |
+-----------------------+---------------+------+-----+---------------------+----------------+
| card_id               | bigint(20)    | NO   |     | NULL                |                | 
| sessionbill           | float         | YES  |     | NULL                |                | 

mysql> desc cc_card;
+---------------------+---------------+------+-----+---------------------+----------------+
| Field               | Type          | Null | Key | Default             | Extra          |
+---------------------+---------------+------+-----+---------------------+----------------+
| id                  | bigint(20)    | NO   | PRI | NULL                | auto_increment | 
| credit              | decimal(15,5) | NO   |     | 0.00000             |                | 

我需要用两个字段的总和更新 cc_card.credit 字段。

我首先尝试使用带有数学运算的 SELECT :

SELECT cc_call.sessionbill+cc_card.credit 
FROM cc_card WHERE cc_card.id = (SELECT cc_call.card_id 
FROM cc_call 
WHERE cc_call.card_id=cc_call.id);

输出错误如下:

ERROR 1054 (42S22): Unknown column 'cc_call.sessionbill' in 'field list'

有什么想法吗？

Answer 1

您应该使用 JOIN:

UPDATE cc_card
JOIN cc_call ON cc_call.card_id = cc_card.id
SET cc_card.credit = cc_call.sessionbill+cc_card.credit