Laravel 5.1 Create or Update on Duplicate

Question

In Laravel 5.1, for MySQL insert, I want to see if the record already exists and update on duplicate or create new if none exists.

I have already searched SO where the answers were for old laravel versions. In one of the old topics it said that a new updateOrCreate() method was added in core last year. But when I try that, I am getting the error:

Integrity constraint violation: 1062 Duplicate entry '1' for key 'app_id' 

This is the query I use:

AppInfo::updateOrCreate(array(
    'app_id' => $postData['appId'],
    'contact_email' => $postData['contactEmail']
));

Where app_id is the unique foreign key in that table and I want to update the record if exists or create a new one. I have tried searching the 5.1 docs and couldn't find the info I need. Can someone guide me here please...

Yvette

github.com/yadakhov/insert-on-duplicate-key A package to do INSERT ON DUPLICATE KEY with laravel. – Correlate

Answer 1

As per the Definition of the Eloquent Model Method "updateOrCreate()"

function updateOrCreate(array $attributes, array $values = []){}

it takes two argument ...

1. one is the attributes using which you want to check in database if the record is present
2. second is the new attribute values that you want to create or update

---

AppInfo::updateOrCreate(['app_id' => $postData['appId']],
                        ['contact_email' => $postData['contactEmail']]);

Leonie

Perfect! That is exactly what I was doing wrong. I presumed setting the unique key in database should take of it automatically but I was wrong. Thank you so much for pointing that out @Leonie It works well now after adding 2 arguments to it. :) – Yvette

@Yvette : Whenever you don't find any documentation online (usually the case for cutting edge beta frameworks) or in doubt, open up their source code file,,they always have very nice documentation and scenario explained before each functions defination – Leonie

If I still haven't created the record on the database, how do I have the id? – Herminiahermione

it can be any column for which you want to check in database, i.e. name, email..etc if row with that value is found, the data in that row will be updated wit new data that you have provided else new record will be created – Leonie

Does this method have a bulk update version to reduce database query overhead? Something equivalent to this SQL: INSERT INTO table (id, col1, col2) VALUES (1, blue, shirt), (2, green, pants), (3, red, hat) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2) – Brynne

this method ignores "trashed" models when searching for duplicates, so you might still get "Duplicate keys" errors from the database if you're trying to create a model and the data from the "unique" keys already exists in a trashed model. (Edit: I'm using Eloquent 5.2, this might have been changed) – Streeto

If anyone's interested here the link to the docs laravel.com/api/6.x/Illuminate/Database/Eloquent/… – Cranwell

Answer 2

I am answering to this question cause I can't find any answer related to ON DUPLICATE KEY UPDATE, although I am using Laravel 5.4. If you look at the updateOrCreate method in the Laravel's core code, you will see after all Laravel is using 2 different queries: one for update and another one for create. Because of this, sometimes you can get duplicated data in the DB. So in some cases it can be useful to write this kind of raw query:

DB::statement("INSERT INTO table_name (col_1, col_2) VALUES (?, ?) ON DUPLICATE KEY UPDATE col_1 = col_1 + 1", ([val_1, val_2]));

Hope it can be useful for someone.

Letha

Answer 3

I created a package to work with MySQL insert on duplicate key.

It may be useful for others:

https://packagist.org/packages/yadakhov/insert-on-duplicate-key

Example:

/**
 * Class User.
 */
class User extends Model
{
    use Yadakhov\InsertOnDuplicateKey;
    ...
}

// associative array must match column names
$users = [
    ['id' => 1, 'email' => '[email protected]', 'name' => 'User One'],
    ['id' => 2, 'email' => '[email protected]', 'name' => 'User Two'],
    ['id' => 3, 'email' => '[email protected]', 'name' => 'User Three'],
];

User::insertOnDuplicateKey($users);

Correlate

will it work on unique key also? there is no primary key in my table. there 2 columns which are combine unique. so I want to do mass update of third column base on first 2 column. – Desiraedesire

Answer 4

Add the follow method insertUpdate to your Model

<?php

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract;
use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract;

class User extends Model implements AuthenticatableContract,
                                    AuthorizableContract,
                                    CanResetPasswordContract
{
    use Authenticatable, Authorizable, CanResetPassword;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'users';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name', 'email', 'password'];

    /**
     * The attributes excluded from the model's JSON form.
     *
     * @var array
     */
    protected $hidden = ['password', 'remember_token'];


    public static function insertUpdate(array $attributes = [])
    {
        $model = new static($attributes);

        $model->fill($attributes);

        if ($model->usesTimestamps()) {
            $model->updateTimestamps();
        }

        $attributes = $model->getAttributes();

        $query = $model->newBaseQueryBuilder();
        $processor = $query->getProcessor();
        $grammar = $query->getGrammar();

        $table = $grammar->wrapTable($model->getTable());
        $keyName = $model->getKeyName();
        $columns = $grammar->columnize(array_keys($attributes));
        $insertValues = $grammar->parameterize($attributes);

        $updateValues = [];

        if ($model->primaryKey !== null) {
            $updateValues[] = "{$grammar->wrap($keyName)} = LAST_INSERT_ID({$keyName})";
        }

        foreach ($attributes as $k => $v) {
            $updateValues[] = sprintf("%s = '%s'", $grammar->wrap($k), $v);
        }

        $updateValues = join(',', $updateValues);

        $sql = "insert into {$table} ({$columns}) values ({$insertValues}) on duplicate key update {$updateValues}";

        $id = $processor->processInsertGetId($query, $sql, array_values($attributes));

        $model->setAttribute($keyName, $id);

        return $model;
    }
}

You can use:

App\User::insertUpdate([
    'name' => 'Marco Pedraza',
    'email' => '[email protected]'
]);

The next query it will be executed:

insert into `users` (`name`, `email`, `updated_at`, `created_at`) values (?, ?, ?, ?) on duplicate key update `id` = LAST_INSERT_ID(id),`name` = 'Marco Pedraza',`email` = '[email protected]',`updated_at` = '2016-11-02 01:30:05',`created_at` = '2016-11-02 01:30:05'

The method automatically add/remove the Eloquent timestamps if you have enabled or disabled.

Geniality

Hi, welcome to SO. Please don't just dump code, explain your train of thought so people can better understand your answer. Thanks. – Dissuasive

Useful function that seems to be working, unlike the updateOrCreate function this one also updates the timestamps. However why does it update both? It should only update the updated_at, currently it also updates the created_at. And is there any way to use this function with bulk inserts? That's why I came looking for this otherwise the updateOrCreate function seemed to work ok aside from the fact it didn't automatically update the timestamps. – Oshaughnessy

will it work on unique key also? there is no primary key in my table. there 2 columns which are combine unique. so I want to do mass update of third column base on first 2 column. – Desiraedesire

Answer 5

To use laravel function updateOrCreate you need auto increment id in your table.

what they are doing is

select id from your_table where your_attributes

after that get auto increment id

then

update your_table set your_values where field_id

Mosley

Just when I wanted to use a composite key with no auto increment id.. – Kristy

You need primary field name id. They take protected $primaryKey = 'id'; from model or else you can override in your model. – Mosley

Answer 6

In my case this was caused by a simple mismatch on the auto incrementing key in my model table.

Getting the current max id:

SELECT max(id) from companies

Then setting the sequence value one higher:

select setval('companies_id_seq', 164);

eliminated the error.

Bergmann