MySQL中You can't specify target table for update in FROM clause错误的意思是说,不能先select出同一表中的某些值,再update这个表(在同一语句中)。 例如下面这个sql:
update tms_jyxx_mx set gg_dm = '1004' where jyxx_id in (SELECT t1.jyxx_id as jyxx_id FROM tms_jyxx t1, tms_jyxx_mx t2 WHERE t1.sjmc = '保定白沟新城铭磊加油站' AND t1.jyxx_id = t2.jyxx_id AND t2.ljqh = '14' AND t2.jysj >= '2019-04-30 10:17:22' AND t2.jysj <= '2019-09-26 23:59:59' ORDER BY t2.jysj desc)
改成下面的就行了:
update tms_jyxx_mx set gg_dm = '1004' where jyxx_id in (SELECT tt.jyxx_id from (SELECT t1.jyxx_id as jyxx_id FROM tms_jyxx t1, tms_jyxx_mx t2 WHERE t1.sjmc = '保定白沟新城铭磊加油站' AND t1.jyxx_id = t2.jyxx_id AND t2.ljqh = '14' AND t2.jysj >= '2019-04-30 10:17:22' AND t2.jysj <= '2019-09-26 23:59:59' ORDER BY t2.jysj desc) tt)
也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于MySQL,MsSql和Oracle不会出现此问题。