oracle中可以执行的一句sql,但是在mysql中不行的
update tms_order too set too.DELIVERY_TYPE = 'CITY' WHERE too.id IN(
select o.id from tms_order o
LEFT JOIN tms_translocation tt on tt.id = o.TO_LOCATION_ID
LEFT JOIN tms_city tc on tc.id = tt.city_id
LEFT JOIN tms_translocation ft on ft.id = o.FROM_LOCATOIN_ID
LEFT JOIN tms_city fc on fc.id = ft.city_id
where o.CONSIGNER_ID <> 8
and tc.id = fc.ID
and tc.id is not NULL
and o.TO_LOCATION_ID is not NULL
)
两个数据库都可以执行的一句sql,要达到的目的和上一句一致
create table tmp as
select o.id as o_id from tms_order o
LEFT JOIN tms_translocation tt on tt.id = o.TO_LOCATION_ID
LEFT JOIN tms_city tc on tc.id = tt.city_id
LEFT JOIN tms_translocation ft on ft.id = o.FROM_LOCATOIN_ID
LEFT JOIN tms_city fc on fc.id = ft.city_id
where o.CONSIGNER_ID <> 8
and tc.id <> fc.ID
and tc.id is not NULL
and o.TO_LOCATION_ID is not NULL;
UPDATE tms_order set DELIVERY_TYPE = 'NYMPHUOUTSIDE' WHERE id IN (select o_id from tmp);
drop table tmp;