MySQL - SELECT 결과로 UPDATE, JOIN 하여 UPDATE

 

SELECT 결과로 UPDATE 하기

-- 형식
UPDATE 테이블A AS A,
(select문) AS B
SET A.칼럼 = 'value' -- 또는 SET A.칼럼 = B.칼럼 등등
WHERE A.key = B.key

-- 예시
UPDATE sj_csv_stock AS cs, (
SELECT ITEM_CODE, YEAR_CODE, SEASON FROM sj_master_goods) AS mg
SET cs.ITEM_CODE=mg.ITEM_CODE, cs.YEAR_CODE=mg.YEAR_CODE, cs.SEASON=mg.SEASON
WHERE cs.CS_NO=mg.CS_NO AND cs.SEASON IS NULL

 

테이블을 JOIN 하여 UPDATE 하기

-- 형식
UPDATE 테이블A AS A
INNER JOIN 테이블B AS B ON A.key = B.key
SET A.칼럼 = 'value'
WHERE 조건

-- 예시
UPDATE lt_job_order AS jo
INNER JOIN sj_master_goods AS mg ON jo.jo_id_code=mg.SAP_CODE
SET jo.size_case='y' -- 또는 SET A.칼럼 = B.칼럼 등등
WHERE jo.jo_id_code != '' AND mg.SAP_CODE != ''