MySQL Error 1093 : You can't specify target table 'tablename' for update in FROM clause

원인

MySQL 은 Oracle 과는 달리 UPDATE 나 DELETE 시 자기 테이블의 데이타를 바로 사용 못하므로 아래와 같은 SQL 을 실행시 1093 에러가 발생함.

DELETE
FROM cwd_group
WHERE id IN
    (SELECT DISTINCT a.id ext_id
     FROM cwd_group a
     JOIN cwd_group b ON a.group_name=b.group_name
     JOIN cwd_directory d ON d.id=a.directory_id
     WHERE a.directory_id != b.directory_id
       AND directory_name = 'My JIRA Server');

SQL

처리

Sub Query 를 하나 더 넣고 sub query 결과를 임시 테이블로 만든후에 실행하면 해결됨.

아래 예제처럼 SELECT ext_id FROM 뒤에 오는 sub query 의 결과를 tmp 라는 임시 테이블에 저장하여 사용

DELETE
FROM cwd_group
WHERE id IN
    (SELECT ext_id
     FROM
       (SELECT DISTINCT a.id ext_id
        FROM cwd_group a
        JOIN cwd_group b ON a.group_name=b.group_name
        JOIN cwd_directory d ON d.id=a.directory_id
        WHERE a.directory_id != b.directory_id
          AND directory_name = 'My JIRA Server') tmp) ;

SQL

자기 테이블의 sub query 결과를 받아서 반영할 경우 아래와 같은 서브 쿼리 대신 를 실행할 경우 1093 에러를 접하게 된다.

UPDATE tbl1
set name = concat(name, 'aa')
where id in (select id from tbl1 where name is not null);

CODE

단일 테이블일 경우 아래와 같이 sub query 의 결과를 임시 테이블(tbl1_result)로 만들어서 한 번 더 감싸도록 서브 쿼리를 작성하면 됨.

UPDATE tbl1
set name = concat(name, 'aa')
where id in (
    select tbl1_alias.nid
    from (
             select id nid
             from tbl1
             where name is not null
         ) tbl1_alias
);

CODE

Ref

mysql-trouble-shooting