今天写一段SQL,如下,
DELETE FROM test_zb_scene WHERE sceneid_bigint IN (
SELECT a.`sceneid_bigint` FROM test_zb_scene a WHERE EXISTS (SELECT scenecode_varchar FROM zb_scene b WHERE a.`scenecode_varchar`=b.`scenecode_varchar`)
DELETE FROM test_zb_scene WHERE sceneid_bigint IN (
SELECT a.`sceneid_bigint` FROM test_zb_scene a WHERE EXISTS (SELECT scenecode_varchar FROM zb_scene b WHERE a.`scenecode_varchar`=b.`scenecode_varchar`)
)
执行报错,You can't specify target table 'test_zb_scene' for update in FROM clause,看意思是同一条语句,不能先select出某些值再update,查度娘说是mysql才有的异常, 规避方法也很简单,用select把查询语句再包裹一层即可。
DELETE FROM test_zb_scene WHERE sceneid_bigint IN (
SELECT sceneid_bigint FROM (
SELECT a.`sceneid_bigint` FROM test_zb_scene a WHERE EXISTS (SELECT scenecode_varchar FROM zb_scene b WHERE a.`scenecode_varchar`=b.`scenecode_varchar`)
) a
)