[MySQL] Table ~ is specified twice, both as a target for 'UPDATE' and as a separate source for data

2021.11.30

Company data에 일괄적으로 update 치려다 발생

 

오류발생 쿼리문

 

UPDATE BP_COMPANY 
SET 
PresidentName = '', RegistNum = ''  
WHERE CompId IN (SELECT A.CompId
                  FROM BP_COMPANY A
                  RIGHT JOIN(
                            SELECT D.CompId
                            FROM (SELECT D.CompId, COUNT(D.EntryId) AS '총회선수'
                                  FROM BP_PRODENTRY D
                                  WHERE D.UseFlag = 'Y'
                                  GROUP BY D.CompId
                                 ) D
                            LEFT JOIN
                                  (SELECT C.CompId, COUNT(Case When C.ProgState = 4 then 1 When C.ProgState = 5 then 1 end) as '해지해제회선'
                                   FROM BP_PRODENTRY C
                                   WHERE C.UseFlag = 'Y'
                                   GROUP BY C.CompId
                                   ) C
                            ON D.CompId = C.CompId
                            WHERE C.해지해제회선 >= D.총회선수
                            GROUP BY D.CompId
                            ) B
                 ON A.CompId = B.CompId
                 )

 

 

CompId를 구한 서브 쿼리에서 Update 치려는 테이블의 칼럼 data를 사용해서 문제 발생

동일 테이블 다중 select 할 땐 종종 이때문에 문제가 발생하고 이렇게 해결하면 됐었던걸 또 잊어버림.

해결 쿼리문

 

UPDATE BP_COMPANY 
SET 
PresidentName = '', 
RegistNum = ''
WHERE CompId IN (SELECT * FROM
                    (SELECT A.CompId
                      FROM BP_COMPANY A
                      RIGHT JOIN(
                                SELECT D.CompId
                                FROM (SELECT D.CompId, COUNT(D.EntryId) AS '총회선수'
                                      FROM BP_PRODENTRY D
                                      WHERE D.UseFlag = 'Y'
                                      GROUP BY D.CompId
                                     ) D
                                LEFT JOIN
                                      (SELECT C.CompId, COUNT(Case When C.ProgState = 4 then 1 When C.ProgState = 5 then 1 end) as '해지해제회선'
                                       FROM BP_PRODENTRY C
                                       WHERE C.UseFlag = 'Y'
                                       GROUP BY C.CompId
                                       ) C
                                ON D.CompId = C.CompId
                                WHERE C.해지해제회선 >= D.총회선수
                                GROUP BY D.CompId
                                ) B
                     ON A.CompId = B.CompId
                     ) bp_comp
                 )