mysql中update更新语法问题

戦無悔 2016-05-16 11:21:47

UPDATE test1 a
INNER JOIN test2 b ON b.id = a.order_id
SET a.handing_fee = FORMAT(b.pay_money * 0.006, 2)
WHERE
b.paytype_name = '银行卡支付'
AND b.handing_fee IS NULL;

执行错误

...全文

1044 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

booker 2016-05-18

打赏
举报

可用insert into ... ON DUPLICATE KEY UPDATE改写update语句

戦無悔 2016-05-16

打赏
举报

[SQL]UPDATE test1 a INNER JOIN test2 b ON b.id = a.order_id SET a.handing_fee = FORMAT(b.pay_money * 0.006, 2) WHERE b.paytype_name = '银行卡支付' AND b.handing_fee IS NULL; [Err] 1064 - multi table related update not supported,tables:[TEST1, TEST2]

ACMAIN_CHM 2016-05-16

打赏
举报

错误提示是什么？

LongRui888 2016-05-16

打赏
举报

mysql中也不能够在set，where中使用任何子查询，这样也会报错。所以，你改成这样：

UPDATE test1 a,
(select * from test2 )b
SET a.handing_fee = FORMAT(b.pay_money * 0.006, 2)
WHERE b.id = a.order_id
and b.paytype_name = '银行卡支付'
AND b.handing_fee IS NULL;

LongRui888 2016-05-16

打赏
举报

MYSQL中不支持在update语句中使用join。

iris4649 2016-05-16

打赏
举报

试试看这个？

update test1 a
set  a.handing_fee=(select FORMAT(b.pay_money * 0.006, 2) from test2 b where b.id=a.order_id and b.paytype_name = '银行卡支付'  AND b.handing_fee IS NULL)
where a.order_id in (select id from test2 where paytype_name = '银行卡支付'  AND handing_fee IS NULL)