You can’t specify target in MySQL The error of table for update in FROM clause means that you cannot select some values in the same table first, and then update this table (in the same statement). For example, the following 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 = 'Minglei Gas Station, Baigou New Town, Baoding'
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)
Just change it to the following:
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 = 'Minglei Gas Station, Baigou New Town, Baoding'
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)
In other words, pass the selected result through the middle The table is selected again, so that the error is avoided. Note that this problem only occurs in MySQL, MsSql and Oracle will not have this problem.
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 = 'Minglei Gas Station, Baigou New Town, Baoding'
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 = 'Minglei Gas Station, Baigou New Town, Baoding'
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)