update cargotype ct set ct.Pid =
(
select c.CargoTypeId as Pid
from cargoinfo c, cargotype t
where t.TypeName = c.Sku and t.Pid = 42 and c.CargoTypeId <> 42
and ct.CargoTypeId = t.CargoTypeId
)
总报
[Err] 1093 - You can't specify target table 'ct' for update in FROM clause网上找了很大说是要弄个临时表或者再套子查询,都试了不行。
是不是要弄程序才能完成用子查询的记录来更新的目的SQLmysql
update cargotype ct INNER JOIN cargoinfo c
ON
Ct.TypeName = c.Sku and Ct.Pid = 42 and c.CargoTypeId <> 42
set ct.Pid = c.CargoTypeId
select t.CargoTypeId,c.CargoTypeId as Pid
from cargoinfo c, cargotype t
where t.TypeName = c.Sku and t.Pid = 42 and c.CargoTypeId <> 42
) x
set ct.Pid = x.Pid
where ct.CargoTypeId = x.CargoTypeId
set t.pid=c.CargoTypeId
from cargoinfo c, cargotype t
where t.TypeName = c.Sku and t.Pid = 42 and c.CargoTypeId <> 42
and ct.CargoTypeId = t.CargoTypeId