How to set bindings on a raw update query laravel

Question

I want to update multiple rows with one update query. I can do this without bindings like such:

Product::whereIn('id',[1,2,3])->update([
    'stock' => DB::raw('CASE id WHEN 1 THEN 1000 WHEN 2 THEN 1001 WHEN 3 THEN 1003 END')
]);

I would however like to use data bindings for the CASE statement. I have tried:

Product::whereIn('id',[1,2,3])->setBindings([1,1,2,2,3,3])->update([
    'stock' => DB::raw('CASE id WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END')
]);

Product::whereIn('id',[1,2,3])->update([
    'stock' => DB::raw('CASE id WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END')
])->setBindings([1,1,2,2,3,3]);

Which both produce the error:

Illuminate/Database/QueryException with message 'SQLSTATE[HY093]: Invalid parameter number (SQL: update `products` set `stock` = CASE id WHEN 2020-03-19 08:58:16 THEN 1 WHEN 2 THEN 3 WHEN ? THEN ? END, `products`.`updated_at` = ? where `id` in (?, ?, ?) and `products`.`deleted_at` is null)'

I have also tried:

Product::whereIn('id',[1,2,3])->update([
    'stock' => DB::raw('CASE id WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END')
         ->setBindings([1,1,2,2,3,3])
]);

Which produces the following error:

PHP Error: Call to undefined method Illuminate/Database/Query/Expression::setBindings() in Psy Shell code on line 1

user_1788771 · Answer

Looking more closely at the error messages I was able to figure out that the issue was that the default bindings for the timestamp in the update statement and the parameters of the whereIn clause were overriding the bindings I set manually. My solution was to switch the whereIn clause to whereRaw and include updated_at inside the update statement:

Product::setBindings([1,1,2,2,3,3,1,2,3])->
    whereRaw('`id` in (?, ?, ?)')->
    update([
        'stock' => DB::raw('case `id` WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? end'),
        'updated_at' => DB::raw('now()'),
    ]);

How to set bindings on a raw update query laravel

1 Answers