[MySQL] 여러 레코드를 각각 다른 값으로 UPDATE하기

UPDATE 쿼리로는 여러 레코드를 같은 값으로만 수정할 수 있기에, 여러 개를 각각 다른 값으로 수정해주어야 하는 경우에는 쿼리 한 방으로 처리할 수 없다고 생각했다.

그러나 아래와 같은 방식을 사용하면 가능해용

---

INSERT INTO ... ON DUPLICATE KEY UPDATE & VALUES

VALUES 함수는 해당 컬럼에 삽입될 값을 의미한다고 한다. 때문에 Id가 PK인 my_table이 있다고 할 때, Id가 1, 2, 3인 컬럼의 순서를 각각 0, 1, 2로 갱신하는 쿼리는 다음과 같이 작성할 수 있다.

INSERT INTO my_table (Id, `Order`) 
VALUES (1, 0), (2, 1), (3, 2) 
ON DUPLICATE KEY UPDATE my_table.`Order` = VALUES(my_table.`Order`);

당연히 테이블에 해당 레코드가 없다면 삽입될 것이고, 있다면 순서를 의미하는 Order 컬럼의 값만을 수정한다.

Row Constructor 문법

MySQL 8.0부터 지원되며, 아래와 같이 레코드 생성 문법을 활용하면 여러 레코드를 서로 다른 값으로 수정할 수 있다.

UPDATE my_table mt
INNER JOIN (
	VALUES ROW(1, 0), ROW(2, 1), ROW(3, 2)
) expected_order (Id, `Order`) ON expected_order.Id = mt.Id
SET mt.`Order` = expected_order.`Order`;

---

덧붙이자면 초기에는 INSERT IGNORE INTO와 REPLACE INTO를 고려했는데, 각각 다음과 같은 특징이 있기에 내가 원하는 결과와 맞지 않아 사용하지 않았다.

• INSERT IGNORE INTO: Key가 겹치는 레코드의 삽입을 무시하므로 갱신되지 않는다.
• REPLACE INTO: 조건에 맞는 레코드들을 제거한 후 새로 생성하기 때문에 Key가 변조된다.

참고

[MySQL]중복 데이터를 관리하는 다양한 방법(INSERT IGNORE, ON DUPLICATED UPDATE, REPLACE INTO)

[MySQL8.0] row constructor 여러 레코드를 서로 다른 값으로 업데이트하는 방법

공식문서는 안 찾아보고 다른 분의 블로그 링크만 박아두는게 개발자가 할 일일까