DB 1093 error

• 설립연도 10년 이내
• 매출액 100억 이하
• 직원 수 30명 이하
  위 조건을 만족하는 기업의 company_classification을 "STARTUP"으로 수정하는 스크립트를 작성하고 있었습니다.

update company set company_classification = "STARTUP"
    where company_number in
          (((select company_number
             from company
             where LENGTH(established_at) = 4
               and established_at >= date_format(curdate(), '%Y') - 10)
            union all
            (select company_number
             from company
             where LENGTH(established_at) = 6
               and established_at >= substr(date_format(curdate(), '%Y%m'), 1, 4) - 10)
            union all
            (select company_number
             from company
             where LENGTH(established_at) = 8
               and established_at >= substr(date_format(curdate(), '%Y%m%d'), 1, 4) - 10))
           union (select company_number from company where annual_sales <= 1000000000)
           union (select company_number from company where worker_count <= 30));
           

위 쿼리 작동 시 굉장히 유명한 1093 오류가 발생하였습니다.

[HY000][1093] You can't specify target table 'company' for update in FROM clause

아래와 같이 쿼리문 수정 후 작동 시 정상적으로 수행되는 것을 확인할 수 있었습니다.

update company set company_classification = "STARTUP"
    where company_number in
          (select r.company_number
           from ((((select company_number
             from company
             where LENGTH(established_at) = 4
               and established_at >= date_format(curdate(), '%Y') - 10)
            union all
            (select company_number
             from company
             where LENGTH(established_at) = 6
               and established_at >= substr(date_format(curdate(), '%Y%m'), 1, 4) - 10)
            union all
            (select company_number
             from company
             where LENGTH(established_at) = 8
               and established_at >= substr(date_format(curdate(), '%Y%m%d'), 1, 4) - 10))
           union (select company_number from company where annual_sales <= 1000000000)
           union (select company_number from company where worker_count <= 30))) r
               );