我对现有的mysql数据库结构进行了一些更改，并希望使用另一个表中的id将旧值更新为表中的新字段。结构如下：
表格1
+---------+--------+
| sale_id | type |
+---------+--------+
| 110 | credit |
| 111 | cash |
| 112 | credit |
+---------+--------+
表2
+---------+--------+---------+-----------------+---------------+
| sale_id | item_id| price | payment_type | total_amount |
+---------+--------+---------+-----------------+---------------+
| 110 | 1 | 20 | | |
| 111 | 2 | 30 | | |
| 112 | 3 | 45 | | |
| 112 | 4 | 15 | | |
+---------+--------+---------+-----------------+---------------+
现在，我要使用table1中的sale_id作为参考来更新table2中的payment_type和total_amount。
table2更新后的预期结果
+---------+--------+---------+-----------------+---------------+
| sale_id | item_id| price | payment_type | total_amount |
+---------+--------+---------+-----------------+---------------+
| 110 | 1 | 20 | credit | 20 |
| 111 | 2 | 30 | cash | 30 |
| 112 | 3 | 45 | credit | 45 |
| 112 | 4 | 15 | credit | 15 |
+---------+--------+---------+-----------------+---------------+
我当前的尝试不起作用，并引发错误。
CREATE TABLE table1(
sale_id INTEGER NOT NULL PRIMARY KEY
,type VARCHAR(6) NOT NULL
);
INSERT INTO table1(sale_id,type) VALUES (110,'Credit');
INSERT INTO table1(sale_id,type) VALUES (111,'Cash');
INSERT INTO table1(sale_id,type) VALUES (112,'Credit');
CREATE TABLE table2(
sales_id INTEGER NOT NULL PRIMARY KEY
,item_id VARCHAR(6) NOT NULL
,price VARCHAR(6) NOT NULL
,payment_type VARCHAR(6) NOT NULL
,total_amount VARCHAR(6) NOT NULL
);
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (110,1,20,'','');
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (111,2,30,'','');
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (112,3,45,'','');
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (112,4,15,'','');
UPDATE table2 SET total_amount =
(SELECT price
FROM table2
INNER JOIN table1 ON sale_id = sales_id
);
UPDATE table2 SET payment_type =
(SELECT type
FROM table2
INNER JOIN table1 ON sale_id = sales_id
);
这就是更新/联接语法：
update table2 t2
inner join table1 t1 on t1.sale_id = t2.sale_id
set t2.payment_type = t1.type, t2.total_amount = t2.price
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句