MySql 여러 행을 한번에 update (한방쿼리)
- sql 한방 쿼리 , multiple record(row) update one query
- 여러 개 레코드를 동시에 변경 업데이트 하는 방법
//------------------------------
* How to update multiple rows at once in MySQL?
https://tableplus.com/blog/2018/11/how-to-update-multiple-rows-at-once-in-mysql.html
- 기본 (여러번 쿼리)
UPDATE students SET score1 = 5, score2 = 8 WHERE id = 1;
UPDATE students SET score1 = 10, score2 = 8 WHERE id = 2;
UPDATE students SET score1 = 8, score2 = 3 WHERE id = 3;
//-------------------------------
* JOIN ... UNION 이용 <===추천
- 1줄도 가능
UPDATE students s
JOIN (
SELECT 1 as id, 5 as new_score1, 8 as new_score2
UNION ALL
SELECT 2, 10, 8
UNION ALL
SELECT 3, 8, 3
) vals ON s.id = vals.id
SET score1 = new_score1, score2 = new_score2;
//-------------------------------
* INSERT INTO 이용
- 키가 설정되어 있어야 하는 제한있음
- 없는 경우 자동 추가됨(주의)
INSERT INTO students
(id, score1, score2)
VALUES
(1, 5, 8),
(2, 10, 8),
(3, 8, 3)
ON DUPLICATE KEY UPDATE
score1 = VALUES(score1),
score2 = VALUES(score2);
//-----------------------------------
Updating multiple rows with different values in one query
- 기본 (여러번 쿼리)
UPDATE mytable SET fruit='orange', drink='water', food='pizza' WHERE id=1;
UPDATE mytable SET fruit='strawberry', drink='wine', food='fish' WHERE id=2;
//-----------------
* CASE 이용
UPDATE mytable SET
fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
drink = CASE WHEN id=1 THEN 'water' ELSE 'wine' END,
food = CASE WHEN id=1 THEN 'pizza' ELSE 'fish' END
WHERE id IN (1,2);
//-----------------
* IF 이용
UPDATE mytable SET
fruit = IF(id=1,'orange','strawberry'),
drink = IF(id=1,'water','wine'),
food = IF(id=1,'pizza','fish')
WHERE id IN (1,2);