README

Postgres 批量更新。

此查询用于 PostgreSQL 的批量更新。您可以使用此查询一次性更新多行。

update "transactions" as "t"
set "value" = "m"."value"::decimal
from (values (1, 10), (2, 20), (3, 30)) as "m" (id, value)
where "m"."id"::bigint = "t"."id"

在查询构建器中添加了新的 joinFrom 方法，以简化 SQL 构建。

安装

composer require johdougss/laravel-mass-update

在 app.php 中配置

'providers' => ServiceProvider::defaultProviders()->merge([
    ...

    Johdougss\Database\DatabaseMassUpdateServiceProvider::class,
])->toArray(),

用法

joinFrom

DB::table('transactions as t')
    ->joinFrom([['id' => 1, 'value' => 10], ['id' => 2, 'value' => 20]], 'm', DB::raw('m.id::bigint'), '=', 't.id')
    ->updateFrom([
        'value' => DB::raw('m.value::decimal'),
    ]);

示例 1

创建迁移

Schema::create('transactions', function (Blueprint $table) {
    $table->id();
    $table->decimal('value', 12, 2);
    $table->integer('type')->default(1);
    $table->string('status', 10)->default('pending');
    $table->timestamp('date');
    $table->timestamps();
});

使用 join laravel 的值

 $values = [
    [
        'id' => 1,
        'value' => 20,
    ],
    [
        'id' => 2,
        'value' => 30,
    ],
     [
        'id' => 3,
        'value' => 30,
    ],
];

DB::table('transactions as t')
    ->join(DB::raw('(values (1,10), (2, 20), (3, 30) ) as mu (id, value, date)'), 'mu.id', '=', 't.id')
    ->updateFrom([
        'value' => DB::raw('(mu.value::decimal + 1)::decimal'),
        'date' => DB::raw('mu.date::timestamp'),
        'type' => 2,
        'status' => DB::raw('case t.id when 1 then \'paid\' else t.status end'),
]);

使用 joinFrom laravel 的值

 $values = [
    [
        'id' => 1,
        'value' => 20,
    ],
    [
        'id' => 2,
        'value' => 30,
    ],
     [
        'id' => 3,
        'value' => 30,
    ],
];


DB::table('transactions as t')
    ->joinFrom($values, 'm', DB::raw('m.id::bigint'), '=', 't.id')
    ->updateFrom([
        'value' => DB::raw('m.value::decimal'),
    ]);

输出 SQL

update "transactions" as "t"
set "value" = m.value::decimal
from (values (?, ?), (?, ?), (?, ?)) as m (id, value)
where m.id::bigint = "t"."id"

调试

array:1 [
  0 => array:3 [
    "query" => "update "transactions" as "t" set "value" = m.value::decimal from (values (?, ?), (?, ?), (?, ?)) as m (id, value) where m.id::bigint = "t"."id""
    "bindings" => array:6 [
      0 => 1
      1 => 20
      2 => 2
      3 => 30
      4 => 3
      5 => 30
    ]
    "time" => 2.5
  ]
]

示例 2

 $values = [
    [
        'id' => 1,
        'date' => Carbon::now(),
        'value' => 20,
    ],
    [
        'id' => 2,
        'date' => '2023-01-02',
        'value' => 30,
    ],
];


DB::table('transactions as t')
    ->joinFrom($values, 'm', DB::raw('m.id::bigint'), '=', 't.id')
    ->updateFrom([
        'value' => DB::raw('(m.value::decimal + 1)::decimal'),
        'date' => DB::raw('m.date::timestamp'),
        'type' => 2,
        'status' => DB::raw('case t.id when 1 then \'paid\' else t.status end'),
    ]);

输出 SQL

update "transactions" as "t"
set "value" = (m.value::decimal + 1)::decimal,
    "date"   = m.date::timestamp,
    "type"   = ?,
    "status" = case t.id when 1 then 'paid' else t.status
end
from (values (?, ?, ?), (?, ?, ?)) as m (id, date, value)
where m.id::bigint = "t"."id"

调试

array:1 [ 
  0 => array:3 [
    "query" => "update "transactions" as "t" set "value" = (m.value::decimal + 1)::decimal, "date" = m.date::timestamp, "type" = ?, "status" = case t.id when 1 then 'paid' else t.status end from (values (?, ?, ?), (?, ?, ?)) as m (id, date, value) where m.id::bigint = "t"."id""
    "bindings" => array:7 [
      0 => 2
      1 => 1
      2 => Carbon\Carbon @1689177399^ {#817
        ...
        date: 2023-07-12 15:56:39.887268 UTC (+00:00)
      }
      3 => 20
      4 => 2
      5 => "2023-01-02"
      6 => 30
    ]
    "time" => 6.6
  ]
]