[MYSQL] SELECT 결과 UPDATE 시키기

SELECT 한 여러행의 결과를 UPDATE 문으로 한번에 UPDATE 시키는 방법

 

UPDATE  metronome.t_selenium_info AS t1
INNER JOIN
(SELECT
        seq,
    country,
    (CASE
        WHEN country = 'AUSTRALIA' THEN 'AUD'
        WHEN country = 'BANGLADESH' THEN 'BDT'
        WHEN country = 'CAMBODIA' THEN 'USD'
        WHEN country = 'CANADA' THEN 'CAD'
        WHEN country = 'CHINA' THEN 'CNY'
        WHEN country = 'INDIA' THEN 'INR'
        WHEN country = 'INDONESIA' THEN 'IDR'
        WHEN country = 'KAZAKHSTAN' THEN 'USD'
        WHEN country = 'KYRGYZSTAN' THEN 'USD'

        WHEN country = 'MALAYSIA' THEN 'MYR'
        WHEN country = 'MONGOLIA' THEN 'MNT'
        WHEN country = 'MYANMAR' THEN 'MMK'
        WHEN country = 'NEPAL' THEN 'NPR'
        WHEN country = 'PAKISTAN' THEN 'PKR'
        WHEN country = 'PHILIPPINES' THEN 'PHP'
        WHEN country = 'RUSSIA' THEN 'RUB'
        WHEN country = 'SINGAPORE' THEN 'SGD'
        WHEN country = 'SRI LANKA' THEN 'LKR'
        WHEN country = 'THAILAND' THEN 'THB'
        WHEN country = 'UK' THEN 'GBP'

        WHEN country = 'USA' THEN 'USD'
        WHEN country = 'UNITED STATES' THEN 'USD'
        WHEN country = 'UZBEKISTAN' THEN 'USD'
        WHEN country = 'VIETNAM' THEN 'VND'
        WHEN country = 'UNITED KINGDOM' THEN 'GBP'
        WHEN country = 'JAPAN' THEN 'JPY'
        WHEN country = 'EUR' THEN 'EUR'
        WHEN country = 'GERMANY' THEN 'EUR'
    END) AS currency

FROM metronome.t_selenium_info
WHERE competitor =#{parameter}) AS t2
ON t1.seq = t2.seq
SET t1.currency = t2.currency
WHERE t1.seq = t2.seq

 

다음과 같이 JOIN 을 시켜 UPDATE 문 작성