CODEWITHSUNDEEP
laraveleloquent
Catto
Catto

Reputation: 527

Bulk insert data if not exists & update data if exists in Laravel

I want to insert data to database if the record is not exists, and update the data in database if the record is exists.

I have 3 variable that stored the request from form:

$employee = $request->employee_id;
example value: "91fc7a4e-6201-4bc2-b2db-196f61d2d0cb"

$locations = $request->location_id;
example value: array:2 [▼
  0 => "ec0987b3-5bd9-11ec-ab79-98fa9b511cca"
  1 => "8300fca0-b63a-4bdb-ae08-8b08a9f58164"
  2 =>  etc
]

$can_checkin_by_location = $request->qr;
example value: array:2 [▼
  0 => "1"
  1 => "1"
  2 => etc
]

The value for $locations and $can_checkin_by_location is an array.

I have tried like code bellow, but I got error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location_id ' in 'field list'

foreach($locations as $key => $local)
  {
    $input['employee_id'] = $employee;
    $input['location_id '] = $local[$key];
    $input['can_checkin_by_location'] = $can_checkin_by_location[$key];

    EmployeeCustomLocation::upsert($input, 'employee_id');
  }

I just discovered upsert method today, so my understanding about upsert is very limited. Usually I'm using updateOrCreate for this kind of case, but I don't know what I have to do if I want to doing bulk insert or update using it

Upvotes: 0

Views: 2145

Answers (1)

Faizan Ali
Faizan Ali

Reputation: 330

You have space after text location_id ['location_id '].

here is updated line:

$input['location_id'] = $local[$key];

If this does not solve error
upsert have three arguments for example

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

1- First argument consists of the values to insert or update.
2- Second argument lists the column(s) that uniquely identify records within the associated table.
3- third and final argument is an array of the columns that should be updated if a matching record already exists in the database.

upsert worked like this:

Where in entire table: departure = Oakland and destination = San Diego then update price = 99.
Similarly where:
departure = Chicago and 'destination = New York then update price = 150.

Upvotes: 0

Related Questions