This package provides macros to run INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE queries on models and pivot tables with Laravel's ORM Eloquent using MySql.

Install this package with composer.

composer require guidocella/eloquent-insert-on-duplicate-key

If you don't use Package Auto-Discovery yet add the service provider to your Package Service Providers in config/app.php.

InsertOnDuplicateKey\InsertOnDuplicateKeyServiceProvider::class,

Call insertOnDuplicateKey or insertIgnore from a model with the array of data to insert in its table.

$data = [
    ['id' => 1, 'name' => 'name1', 'email' => '[email protected]'],
    ['id' => 2, 'name' => 'name2', 'email' => '[email protected]'],
];

User::insertOnDuplicateKey($data);

User::insertIgnore($data);

If you want to update only certain columns, pass them as the 2nd argument.

User::insertOnDuplicateKey([
    'id'    => 1,
    'name'  => 'new name',
    'email' => '[email protected]',
], ['name']);
// The name will be updated but not the email.

You can customize the value with which the columns will be updated when a row already exists by passing an associative array.

In the following example, if a user with id = 1 doesn't exist, it will be created with name = 'created user'. If it already exists, it will be updated with name = 'updated user'.

User::insertOnDuplicateKey([
    'id'    => 1,
    'name'  => 'created user',
], ['name' => 'updated user']);

The generated SQL is:

INSERT INTO `users` (`id`, `name`) VALUES (1, "created user") ON DUPLICATE KEY UPDATE `name` = "updated user"

You may combine key/value pairs and column names in the 2nd argument to specify the columns to update with a custom literal or expression or with the default VALUES(column). For example:

User::insertOnDuplicateKey([
    'id'       => 1,
    'name'     => 'created user',
    'email'    => '[email protected]',
    'password' => 'secret',
], ['name' => 'updated user', 'email]);

will generate

INSERT INTO `users` (`id`, `name`, `email`, `password`)
VALUES (1, "created user", "[email protected]", "secret")
ON DUPLICATE KEY UPDATE `name` = "updated user", `email` = VALUES(`email`)

Call attachOnDuplicateKey and attachIgnore from a BelongsToMany relation to run the inserts in its pivot table. You can pass the data in all of the formats accepted by attach.

$pivotData = [
    1 => ['expires_at' => Carbon::today()],
    2 => ['expires_at' => Carbon::tomorrow()],
];

$user->roles()->attachOnDuplicateKey($pivotData);

$user->roles()->attachIgnore($pivotData);