In Laravel 5.8 how update only a few columns if row exists?

Question

I'm using updateOrInsert to insert a row in DB or update it in case it already exists.

From the manual: https://laravel.com/docs/5.8/queries

Update Or Insert Sometimes you may want to update an existing record in the database or create it if no matching record exists. In this scenario, the updateOrInsert method may be used. The updateOrInsert method accepts two arguments: an array of conditions by which to find the record, and an array of column and value pairs containing the columns to be updated.

The updateOrInsert method will first attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record can not be found, a new record will be inserted with the merged attributes of both arguments:

In my case, I don't want to overwrite (update) all columns but just the updated_at column.

In MySql I use to use INSERT with ON DUPLICATE KEY UPDATE specifying the only columns to be updated.

How can I do this in Laravel with updateOrInsert? Thanks for any suggestion.

DB::table('products')->updateOrInsert(
  ['upc' => $request->get('upc'),],
  ['upc' => $request->get('upc'),
    'name' => $request->get('name'),
    'created_at' => ...,
    'updated_at' => ...]
);

Answer 1

If you look into the code of the Query Builder, you will see that Laravel is executing two queries as well:

/**
* Insert or update a record matching the attributes, and fill it with values.
*
* @param  array  $attributes
* @param  array  $values
* @return bool
*/
public function updateOrInsert(array $attributes, array $values = [])
{
    // See if record exists (query 1)
    if (! $this->where($attributes)->exists()) {
        // Do an insert (query 2)
        return $this->insert(array_merge($attributes, $values));
    }

    // Do an update (query 2)
    return (bool) $this->take(1)->update($values);
}

You can copy this code and change the:

return (bool) $this->take(1)->update($values);

to

return (bool) $this->take(1)->update(['updated_at' => '2019-08-31 12:34:45']);

If you do want to use the INSERT with ON DUPLICATE KEY UPDATE you should use a RAW query. The Laravel query builder doesn't support a MySQL only method.

The raw query should look something like this:

DB::statement('INSERT INTO `products` (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE `updated_at` = NOW();');