mysql更新同一个表的同个字段

原创

用户1571008

发布于 2022-11-18 10:29:56

2.2K0

发布于 2022-11-18 10:29:56

需求：数据表express_log的字段option_time，将状态为30的更新为状态为0的加上2秒

EXPLAIN update `express_log` d inner join  (SELECT a.option_time  FROM `express_log` a left join `order` b on a.order_id = b.id where b.created_at > '2022-11-15 17:00:00' and b.transit_center_id in (105,107)  and a.status =0) c on a.order_id = d.order_id set d.option_time = date_add(c.option_time,interval 1 second) and d.order_id in (SELECT a.order_id  FROM `express_log` a left join `order` b on a.order_id = b.id where b.created_at > '2022-11-15 17:00:00' and b.transit_center_id in (105,107)  and a.status = 30)

报错：

#1054 - Unknown column 'a.order_id' in 'on clause' 原因： 不能先将select出表中的某些值，再update这个表(在同一语句中） 解决：

将查询的数据创建一个临时表去更新同一个表的数据思路： update 表1 a1 inner join (select 字段1,字段2 from 表1 where 条件) a2 on 条件

set a1.字段1 = a2.字段2 最后

update `express_log` d left join (SELECT option_time,order_id FROM `express_log` e left join `order` oo on e.order_id = oo.id where oo.created_at > '2022-11-15 17:00:00' and oo.transit_center_id in (105,107) and oo.express_state >= 60 and e.status =0) c on c.order_id = d.order_id left join `order` o on d.order_id =o.id set d.option_time = date_add(c.option_time, interval 2 second) where o.created_at > '2022-11-15 17:00:00' and o.transit_center_id in (105,107) and o.express_state >= 60 and d.status = 30;

问题解决！参考： https://blog.csdn.net/wslzjr/article/details/106014855 拓展参考： https://blog.csdn.net/weixin_39618121/article/details/115896835

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

https