Laravel insert or update multiple rows

Question

Im new in laravel, and im trying to update my navigation tree. So i want to update my whole tree in one query without foreach.

array(
  array('id'=>1, 'name'=>'some navigation point', 'parent'='0'),
  array('id'=>2, 'name'=>'some navigation point', 'parent'='1'),
  array('id'=>3, 'name'=>'some navigation point', 'parent'='1')
);

I just want to ask - is there posibility in laravel to insert(if new in array) or update my current rows in database?

I want to update all, because i have fields _lft, _right, parent_id in my tree and im using some dragable js plugin to set my navigation structure - and now i want to save it.

I tried to use

Navigation::updateOrCreate(array(array('id' => '3'), array('id'=>'4')), array(array('name' => 'test11'), array('name' => 'test22')));

But it works just for single row, not multiple like i tried to do. Maybe there is another way to do it?

Abdelalim Hassouna · Answer 1 · 2021-03-02T10:43:51.883

19

It's now available in Laravel >= 8.x

The method's first argument consists of the values to insert or update, while the second argument lists the column(s) that uniquely identify records within the associated table. The method's third and final argument is an array of columns that should be updated if a matching record already exists in the database:

Flight::upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);

edited Mar 02 '21 at 10:43

answered Mar 02 '21 at 09:56

Abdelalim Hassouna

671
8
12

To use this, we must add a unique index to our table. Also, could you please lead me through the upsert if the table just has a single column with an ID? – Khyati Bhojawala Apr 21 '23 at 06:28
I am not sure I quite understand your scenario but I presume you use the ID as a second parameter and use the column name you want to change as the third param in the function call. Of course the first item will be the array of new/updated values. – Abdelalim Hassouna Apr 24 '23 at 23:38
Please review my table format for a better idea; https://prnt.sc/-gyO_z-cM0vh – Khyati Bhojawala May 17 '23 at 04:14

score 17 · Answer 2 · edited May 23 '17 at 11:54

I wonder why this kind of feature is not yet available in Laravel core (till today). Check out this gist The result of the query string would look like this: here

I am putting the code here just in case the link breaks in the future, I am not the author:

/**
* Mass (bulk) insert or update on duplicate for Laravel 4/5
* 
* insertOrUpdate([
*   ['id'=>1,'value'=>10],
*   ['id'=>2,'value'=>60]
* ]);
* 
*
* @param array $rows
*/
function insertOrUpdate(array $rows){
    $table = \DB::getTablePrefix().with(new self)->getTable();


    $first = reset($rows);

    $columns = implode( ',',
        array_map( function( $value ) { return "$value"; } , array_keys($first) )
    );

    $values = implode( ',', array_map( function( $row ) {
            return '('.implode( ',',
                array_map( function( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )
            ).')';
        } , $rows )
    );

    $updates = implode( ',',
        array_map( function( $value ) { return "$value = VALUES($value)"; } , array_keys($first) )
    );

    $sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";

    return \DB::statement( $sql );
}

So you can safely have your arrays inserted or updated as:

insertOrUpdate(
    array(
      array('id'=>1, 'name'=>'some navigation point', 'parent'='0'),
      array('id'=>2, 'name'=>'some navigation point', 'parent'='1'),
      array('id'=>3, 'name'=>'some navigation point', 'parent'='1')
    )
);

Just in case any trouble with the first line in the function you can simply add a table name as a second argument, then comment out the line i.e:

function insertOrUpdate(array $rows, $table){
    .....
}

insertOrUpdate(myarrays,'MyTableName');

NB: Be careful though to sanitise your input! and remember the timestamp fields are not touched. you can do that by adding manually to each arrays in the main array.

Saw this and made another version, based on Laravel base classes. https://gist.github.com/tonila/26f6a82c4dbe63d93b22ac67eaee2d6d — Tola, Dec 18 '18 at 16:00
@Tola can you give me an instruction how you implemented that, i'm getting Type error: Too few arguments to function Illuminate\Database\Query\Builder::__construct(), 2 passed in error — Alauddin Ahmed, Oct 08 '19 at 12:44
What laravel version are you using @AlauddinAhmed? At Laravel 5.5 the `Illuminate\Database\Query\Builder` class expects only one mandatory parameter and its `use Illuminate\Database\ConnectionInterface;` Is it possible something has changed? — Oluwatobi Samuel Omisakin, Oct 08 '19 at 13:37
@OluwatobiSamuelOmisakin i've found that third parameter required use Illuminate\Database\Query\Processors\Processor . After adding this as third parameter, the error is gone and data is saving. But now i'm facing another problem. Its not saving duplicate values — Alauddin Ahmed, Oct 09 '19 at 04:46
I think i found a solution. I had to create a unique index to that table. — Alauddin Ahmed, Oct 09 '19 at 06:48
If anyone wants to use this as a function in a base model, you will need to change the line `$table = \DB::getTablePrefix().with(new self)->getTable();` to `$table = \DB::getTablePrefix().with(new static)->getTable();` to get the current class's table name... — Romeo Sierra, Feb 21 '20 at 09:10

score 7 · Answer 3 · answered Jun 08 '19 at 20:50

I've created an UPSERT package for all databases: https://github.com/staudenmeir/laravel-upsert

DB::table('navigation')->upsert(
    [
        ['id' => 1, 'name' => 'some navigation point', 'parent' => '0'],
        ['id' => 2, 'name' => 'some navigation point', 'parent' => '1'],
        ['id' => 3, 'name' => 'some navigation point', 'parent' => '1'],
    ],
    'id'
);

score 3 · Answer 4 · answered Jan 01 '19 at 16:39

Eloquent Style

public function meta(){ // in parent models.
    return $this->hasMany('App\Models\DB_CHILD', 'fk_id','local_fk_id');
}

. . .

$parent= PARENT_DB::findOrFail($id);
$metaData= [];
        foreach ($meta['meta'] as $metaKey => $metaValue) {

            if ($parent->meta()->where([['meta_key', '=',$metaKey]] )->exists()) { 
                $parent->meta()->where([['meta_key', '=',$metaKey]])->update(['meta_value' => $metaValue]);
            }else{
                $metaData[] = [
                    'FK_ID'=>$fkId,
                    'meta_key'=>$metaKey,
                    'meta_value'=> $metaValue
                ];
            }

        }
$Member->meta()->insert($metaData);

score 1 · Answer 5 · answered Nov 29 '16 at 11:14

1

No, you can't do this. You can insert() multiple rows at once and you can update() multiple rows using same where() condition, but if you want to use updateOrCreate(), you'll need to use foreach() loop.

answered Nov 29 '16 at 11:14

Alexey Mezenin

158,981
26
290
279

Ok, but if i use insert it wil make just one query? Multiple inserts should be done in foreach? – Mateusz Kudej Nov 29 '16 at 14:45
@MateuszKudej if you'll pass an array into `insert()` it'll create multiple row with one query. – Alexey Mezenin Nov 29 '16 at 15:09

score 0 · Answer 6 · edited Dec 13 '20 at 14:29

I didn't find a way to bulk insert or update in one query. But I have managed with only 3 queries. I have one table name shipping_costs. Here I want to update the shipping cost against the shipping area. I have only 5 columns in this table id, area_id, cost, created_at, updated_at.

// first get ids from table
$exist_ids = DB::table('shipping_costs')->pluck('area_id')->toArray();
// get requested ids
$requested_ids = $request->get('area_ids');
// get updatable ids
$updatable_ids = array_values(array_intersect($exist_ids, $requested_ids));
// get insertable ids
$insertable_ids = array_values(array_diff($requested_ids, $exist_ids));
// prepare data for insert
$data = collect();
foreach ($insertable_ids as $id) {
$data->push([
    'area_id' => $id,
    'cost' => $request->get('cost'),
    'created_at' => now(),
    'updated_at' => now()
]);
}
DB::table('shipping_costs')->insert($data->toArray());

// prepare for update
DB::table('shipping_costs')
->whereIn('area_id', $updatable_ids)
->update([
    'cost' => $request->get('cost'),
    'updated_at' => now()
]);

score -1 · Answer 7 · answered Nov 30 '16 at 03:01

in your controller use DB; public function arrDta(){

        $up_or_create_data=array(
            array('id'=>2, 'name'=>'test11'),
            array('id'=>4, 'name'=>'test22')

        );

        var_dump($up_or_create_data);
        echo "fjsdhg";

        foreach ($up_or_create_data as $key => $value) {
            echo "key  ".$key;
            echo "<br>";
            echo " id: ".$up_or_create_data[$key]["id"];
            echo "<br>";
            echo " Name: ".$up_or_create_data[$key]["name"];


        if (Navigation::where('id', '=',$up_or_create_data[$key]["id"])->exists()) {
            DB::table('your_table_ name')->where('id',$up_or_create_data[$key]["id"])->update(['name' => $up_or_create_data[$key]["name"]]);
        }else{
            DB::insert('insert into your_table_name (id, name) values (?, ?)', [$up_or_create_data[$key]["id"], $up_or_create_data[$key]["name"]]);
        }

        }

Laravel insert or update multiple rows

7 Answers7

Linked