How to update and create data by method in laravel query builder tutorial | updateOrInsert() and upsert() query

In some time we want to use a single method for both create and update data.Laravel provide some methods for creating and update data base on one and group of more than one unique column.

Laravel query builder methods for update or create data

How to update and insert data by one method in laravel than We will use two query builder methods updateOrInsert and upsert.

1. updateOrInsert()
2. upsert()

Laravel updateOrInsert method

Laravel updateOrInsert method use for both create and update data.In updateOrInsert method first argument we pass unique column with value which are one column or array of column that are unique with and second argument pass array of columns with values that want to update.Thus if first agrument data match in table row it update that row by second argument data else it create new row with that data.

let our employees table contain these columns id, name, email, created_at and updated_at

Controller example EmployeeController.php here we pass a id=7 if employees table contain data on id value 7 than it update data else it create new row with given detail name and email

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class EmployeeController extends Controller{
 public function updateOrCreate(){
    $id = 7;
    $employee = DB::table('employees')
			->updateOrInsert(
			   ['id',$id],
			   ['email' => 'john@example.com', 'name' => 'John']
			);

     return 'data successfully insert';
  }

}

Laravel upsert method

One another method for data update or create is upsert method it use for perform operation on mass data. Upsert method use on array of many table rows. In this method first argument is array of data that insert or update. While the second argument list of columns that are unique define and third argument list of column that are update if unique cloumns data match in table.

see below controller example here if any row id already exist than it update only name and email data else it create new data.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class EmployeeController extends Controller{
    public function updateOrCreate(){
       $id = 7;
       $employee = DB::table('employees')
		        ->upsert([
			   ['id'=>'1','name' => 'anmol sharma', 'email' => 'anmol@gmail.com'],
			   ['id'=>'5','name' => 'advanced web', 'email' => 'advancedweb@gmail.com'],
			], ['id'], ['name', 'email']);

       return 'data successfully insert';
  }

}

You read this tutorial on advanced web tuts. watch all query builder playlist click on sidebar playlist link

Related tutorial links

• laravel query builder introduction tutorial
• part 1 laravel query insert data
• part 2 laravel all type of where conditions in query builder
• part 3 laravel whereDate, whereYear and whereTime qyery method
• part 4 compare two db columns in laravel query builder
• part 6 laravel pluck method get single column value
• part 7 laravel select and addselect query in query builder
• part 5 laravel first and find queries
• part 8 laravel edit and update method query explaination
• Part 9 laravel query for update or insert data by one method
• part 10 laravel delete vs truncate vs drop method
• part 11 gropupby and orderby query tutorial
• Part 12 laravel union of two queries
• part 13 laravel all aggregate functions with example
• part 14 laravel query builder chunk method
• part 15 laravel exists() and doesntExist() method
• part 16 laravel paginate vs simplePaginate method
• part 17 laravel all join methods