Page tree

원인

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');

 

처리

Sub Query 를 하나 더 넣어서 임시 테이블을 만들면 해결됨

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) ;

 

 

Ref