Update multiple rows in one query

workout_id 칼럼을 새로 추가하고 기존에 있던 레코드들 옆에 값을 덧붙이려면 하나하나 update set query를 날리던지 아래처럼 case when then 구문을 써주면 된다.

그냥 실행하면 안전모드를 사용한다고 나온다.

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

워크벤치 상단 네비게이션 바에서 Edit->Preferences->SQL Editor->Safe Updates 해제->OK->workbench restart.

update intensity

set workout_id = case when intensity_id = 1 then 1

when intensity_id = 2 then 2

                    when intensity_id = 3 then 1

                    when intensity_id = 4 then 1

                    when intensity_id = 5 then 1

                    when intensity_id = 6 then 2

                    when intensity_id = 7 then 3

                    when intensity_id = 8 then 3

                    when intensity_id = 9 then 1

                    when intensity_id = 10 then 1

                    when intensity_id = 11 then 3

                    when intensity_id = 12 then 3

                    when intensity_id = 13 then 3

                    when intensity_id = 14 then 1

end

;

출처 https://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query/25674827