カラムXとカラムYの値を入れ替えたい
ひょんなことからあるテーブルについてカラムXとカラムYに入っている値を入れ替えなければならなくなったとします。
そんなとき、安易に
UPDATE `table`
SET `X` = `Y`
, `Y` = `X`;
とやろうとすると…地獄を見ます。
MySQLでは記述された順番に処理を実行するので、どちらも元のY
の値で更新されてしまいます。
そこでちょっと一工夫必要です。
対応方法
1. ユーザー変数を使う
ググると解決策として多く出てきますね。(主観)
以下のサイトを参考にさせていただきました。
MySQLでフィールドの値を交換する
SET @temp = 0;
UPDATE `table`
SET `X` = (@temp:=`X`) * 0 + `Y`
, `Y` = @temp;
数値型の場合ですね。
X
に代入するところで、変数@temp
にも格納しつつ、ゼロ倍してそのままY
の値が入るようにすると。
これはなかなか自力でたどり着けない解法でした。
2. 加減算/結合分割で処理する
以下のサイトを参考にさせていただきました。
MySQLで値の交換
#数値の場合
UPDATE `table`
SET `X` = `X` + `Y`
, `Y` = `X` - `Y`
, `X` = `X` - `Y`;
#文字列の場合
UPDATE `table`
SET `X` = CONCAT(`X`, ':', `Y`)
, `Y` = SUBSTRING_INDEX(`X`, ':', 1)
, `X` = SUBSTRING_INDEX(`X`, ':', -1);
①まずX
に合計(結合)した値を格納し、
②Y
には合計からY
を差し引いて元のX
の値を格納、
③そして改めてX
には合計から入れ替え後のY
=元のX
を差し引き、元のY
の値を格納すると。
プログラムチックで、ある意味馴染みやすい解法でした。
3. 自己結合を使う
他であまり見つからなかったのですが、こんな方法も。
UPDATE `table` t1, `table` t2
SET t1.`X` = t2.`Y`
, t1.`Y` = t2.`X`
WHERE t1.id = t2.id;
自己結合を使うと、冒頭の安易なSQLに近い形で実現できます。
結構シンプルですね。