Oracle转MySQL delete from ... where exists 兼容解决方案

Oracle转MySQL delete from … where exists 兼容解决方案

-- Oracle
DELETE FROM TEMP_DEP T WHERE EXISTS (SELECT R.NAME FROM TEMP_SCORE R WHERE R.NAME = T.NAME); 

-- MySQL
-- delete语句不能有别名，故无法执行以上语句
-- 思路：1、删除别名T，删除后无法达到改造要求，会将TEMP_DEP表的数据全部删除
--      2、不用EXISTS，用子查询代替
	DELETE FROM TEMP_DEP where ID in (select DISTINCT T.ID FROM TEMP_DEP T, TEMP_SCORE R WHERE R.NAME = T.NAME);
-- 以上语句执行会报You can't specify target table 'TEMP_DEP' for update in FROM clause，可以再嵌套一层子查询即可
-- 最终版本
	DELETE FROM TEMP_DEP where ID in (select id from (select DISTINCT T.ID FROM TEMP_DEP T, TEMP_SCORE R WHERE R.NAME = T.NAME) temp);
-- 3、若必须使用EXISTS，则可以用以下语句
	delete from TEMP_DEP where ID in (select id from (select id FROM TEMP_DEP T WHERE EXISTS (SELECT R.NAME FROM TEMP_SCORE R WHERE R.NAME = T.NAME)) temp);