phpで複数レコードを一括で更新する

Question

前提・実現したいこと

arrayのold_orderとDBのorderをマッチさせて、DBを一括でnew_orderの値に更新したいです。
foreachで試しましたが、上手くできませんでした。

他の方法も見つからずに困っています。
解決できる方法があればご教授いただきたいです。

よろしくお願いします。

array:3 [
  0 => array:3 [
    "type" => 1
    "new_order" => 2
    "old_order" => 1
  ]
  1 => array:3 [
    "type" => 1
    "new_order" => 3
    "old_order" => 2
  ]
  2 => array:3 [
    "type" => 1
    "new_order" => 1
    "old_order" => 3
  ]
]

update前
+------+-------+
| type | order |
+------+-------+
|    1 |     1 |
|    1 |     2 |
|    1 |     3 |
+------+-------+

update後
+------+-------+
| type | order |
+------+-------+
|    1 |     2 |
|    1 |     3 |
|    1 |     1 |
+------+-------+

試したこと

foreachを使って下記の流れで更新しましたが、組み合わせによって意図しない更新が発生してしました。

1. new_orderとマッチするorderを0に更新
2. old_orderとマッチするorderをnew_orderに更新
3. orderが0のorderをold_orderに更新

$array = [
  0 => array:3 [
    "type" => 1
    "new_order" => 2
    "old_order" => 1
  ]
  1 => array:3 [
    "type" => 1
    "new_order" => 3
    "old_order" => 2
  ]
  2 => array:3 [
    "type" => 1
    "new_order" => 1
    "old_order" => 3
  ]
]

foreach ($array as $value){
    $db = DB::table("order_number")
        ->where("order_number.type","=", $value["type"])
        ->where("order_number.order","=", $value["old_order"])
        ->get();

    if (!($db[0]["order"] == $value["new_order"])) {
        DB::table("order_number")
            ->where("order_number.type","=", $value["type"])
            ->where("order_number.order", "=", $value["new_order"])
            ->update([
                "order_number.order" => 0
            ]);

         DB::table("order_number")
             ->where("order_number.type","=", $value["type"])
             ->where("order_number.order", "=", $value["old_order"])
             ->update([
                 "order_number.order" => $value["new_order"]
             ]);

         DB::table("order_number")
             ->where("order_number.type","=", $value["type"])
             ->where("order_number.order", "=", 0)
             ->update([
                  "order_number.order" => $value["old_order"]
             ]);
    }
}

補足情報（FW/ツールのバージョンなど）

PHP 7.2.0
Laravel 5.1.46

Comments

[m.ts10806]

実際どのようなコードを組まれたのでしょうか。

Answer 1

type=1に対してorderが1→2,2→3,3→1を一気にしたいということでよいでしょうか？
あまり馴染みがないと思いますがこの手の処理はelt＋filedで行います

• 元データ

create table tbl (id int primary key,`type` int,`order` int);
insert into tbl values(1,1,1),(2,1,2),(3,1,3),(4,1,2),(5,1,2),(6,1,4),(7,2,1),(8,3,2);

• 更新処理

※ごめんなさい、調整しました

update tbl set `order`=elt(field(`order`,1,2,3),2,3,1) 
WHERE `order` IN(1,2,3) and type=1

↓↓↓

update tbl set `order`=coalesce(elt(field(`order`,1,2,3),2,3,1),`order`) 
WHERE type=1

1-5のデータはtypeが1なので更新される、
6のデータはtype=1だけどorderが1,2,3ではないので対象外
7,8のデータはtypeが1じゃないので対象外

追記

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-3' for key ~~~~

おそらくtype,orderに復号ユニーク属性がついていますね。
そうであれば条件が競合するので無理です。
無理を前提でやるなら、たとえばorderがつねにプラスだとすれば一度マイナスデータで
登録してあとから絶対値で更新するとかや利用はあります。

しかし逆にこれはunique属性をわざわざつけて競合しないようにしている設定を
無効にししてしまうので、運用上正しいかどうかはわかりません

• sample

create table tbl (id int primary key,`type` int,`order` int,unique key(`type`,`order`));
insert into tbl values(1,1,1),(2,1,2),(3,1,3)

とりあえずupdate

update tbl set `order`=coalesce(elt(field(`order`,1,2,3),2,3,1),`order`) 
WHERE type=1

当然エラー

無理やりやる

update tbl set `order`=coalesce(elt(field(`order`,1,2,3),-2,-3,-1),`order`) 
WHERE type=1;
update tbl set `order`=abs(`order`);

Comments

[yambejp]

ごめんなさい一部調整しました

[aeofun]

ありがとうございます！
クエリビルダで直接書き、実行されましたが下記のようなエラーが出てしまいました。
データベースの設定を変えずに実現することが難しいでしょうか？

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-3' for key ~~~~

---php
DB::update("
update order_number
set `order` = coalesce(elt(field(`order`,1,2,3),1,3,2),`order`)
where type = 1
");
---

[yambejp]

追記しました。仕様から考えれば無理ですが運用でカバーできるかもしれません。

[yambejp]

ちなみに1,2,3を2,3,1に変えるならelt(field(`order`,1,2,3),1,3,2)
はおかしいのでちゃんと指定してください

[aeofun]

追記ありがとうございます。
下記のエラーは、クエリビルダではabs関数が使えないということなのでしょうか？
代わりになる関数が見つからないです...。

DB::update("
update order_number
set `order` = coalesce(elt(field(`order`,1,2,3),2,3,1),`order`)
where type = 1;
update column_parts
set `order_number` = abs(`order_number`);
");

↓↓↓↓↓↓↓

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update order_number
set `order` = abs(`order`)' at line 4

[yambejp]

回答はよく読みましょう
最初にセットするのがマイナスです
-2,-3,-1

[aeofun]

マイナスでセットしても同じでした...。

[yambejp]

ご利用の環境がわからないのですが
マルチSQLを実行できるのでしょうか？
１文ずつ実行する必要があるかもしれません

いずれにしろロジックとしては、
別のユニークで適当な数値に変更しておき
その後正しい値に置き換え直すという考え方だけ理解ください

[aeofun]

恐らく実行できる環境ではありません。1文ずつ実行してみます。
詳細に回答していただきありがとうございました！

Answer 2

1. new_orderとマッチするorderを0に更新
2. old_orderとマッチするorderをnew_orderに更新
3. orderが0のorderをold_orderに更新

3.で明らかにおかしくなりますよね。

MySQLと決まってるなら、素直にDB::raw()を使えばいいのでは
SQLはこんな感じでできると思う(一括で変える必要があるのでトランザクションは必須でしょうが)

update hoge set order = 
      CASE
            WHEN order = 1 then 2
            WHEN order = 2 then 3
            WHEN order = 3 then 1
            ELSE 0 # 来る予定はない
      END

Comments

[aeofun]

回答ありがとうございます！更新する数字を指定する場合は活用させていただきます！