CODEWITHSUNDEEP
phpmysqlsqllaravelquery-builder
exexe
exexe

Reputation: 196

Connect 2 queries to 1

I have code like this:

$id = 5;
$a = 1;
$b = ($a === 2 ? 1 : 2);

DB::table('table')->where('id', $id)->where('value', $a)->update(['new_value' => 1]);
DB::table('table')->where('id', $id)->where('value', $b)->update(['new_value' => 2]);

Is it possible to make this 2 queries in 1?

Upvotes: 2

Views: 96

Answers (3)

Joseph Silber
Joseph Silber

Reputation: 219920

You cannot use the query builder for this. Use DB::statement instead:

DB::statement('UPDATE table SET new_value = CASE 
               WHEN value = ? THEN ?
               WHEN value = ? THEN ?
               END WHERE id = ?', [
    $a, 1,
    $b, 2,
    $id,
]);

Upvotes: 3

Steve Bauman
Steve Bauman

Reputation: 8668

AFAIK, not with laravel fluent. There are 2 different queries with different update values. You can however make it into a reusable function.

public function updateNewValue($id, $value, $newValue)
{
    return DB::table('table')
        ->where('id', $id)
        ->where('value', $value)
        ->update(['new_value' => $newValue]);
}

Or, you can combine it into one with a switch statement depending on the new_value:

$query = DB::table('table')->where('id', $id);

switch($newValue)
{
    case 1:
        return $query->where('value', $a)->update(['new_value' => $newValue]);
    case 2:
        return $query->where('value', $b)->update(['new_value' => $newValue]);
}

Upvotes: 0

shaishaw
shaishaw

Reputation: 156

In mysql query you can achieve by below query:

$mysqlQuery = "UPDATE table1
SET new_value = IF(id=".$id ." AND value=".$a.",1, IF(id=".$id." AND value=".$b.",2,new_value))";

Note:If you want this mysql query to be written in some framework specific query then you can convert it as per your framework syntax or documentation.

Upvotes: 0

Related Questions