This logic would need to be applied manually since this logic does not exist in the query builder (or at least I couldn't find it). You'd need to go through, check the existence of a particular id and either insert or update the record as needed.

Doing it one by one is easy, I can simply use eloquent and it will solve it.

But I nead it in one query and one foreach

There are methods called firstOrCreate (save model) and firstOrNew (create but dont save) but those are eloquent methods and I don't think they will exactly solve your problem. Are you trying to find by ID only? Or is the ID + Temp pair important? Or are you finding by just temp? If you are querying by temp then you can use the firstOrCreate method:

Model::firstOrCreate(array('temp' => 1));

i think he's looking for something like codeigniters active records insert_batch() and update_batch()

i have been wondering the same thing myself!

@keevitaja can you explain the problem in more detail? I'm unfamiliar with codeigniter

I have several rows on a table I would like to insert/update when exists.

this is the table: item_id, key, val

the primary key is ( item_id, key)

now I would like to insert a bulk of rows at one query, but if one of the rows has an exiting row, i would like only to update the "val" column.

like this:

insert into table (item_id, key, val) values(1, "A", 19) on duplicate key update val=values(val)

if I wouldn't find a solution I would simply run a raw query, but then I should handle the security of the data, from sql injections.

now I use this method:

 foreach ( $sqlInsert as $row ) {
    
        DB::insert(
            'insert into `wedding_data` (`wedding_id`, `key`, `val`) values (?, ?, ?)
            on duplicate key update `val`=values(`val`)',
            array($row['wedding_id'], $row['key'], $row['val'])
        );

    }

this works just fine, just it perform one query for each row, and If I have 20 rows, I'll perform 20 queries instead of one.

Anyone please?

Did anyone come up with a solution for this, I am also looking to do the same.

When it's 20 rows, it's not great, but when you are needing to deal with potentially thousands of rows (or in my case tens of thousands), this is beyond bad.

At the moment I am using a raw sql query, then, as I need the objects, having to do a query after the insert so that I can get the updated objects back.....

Of course you can foreach, but each iteration will perform a sql query...... When you have in excess of tens of thousands of rows, thats just asking for trouble.

$sqlInsert = array(
    array('id'=>1, 'temp'=>1),
    array('id'=>2, 'temp'=>2),
    array('id'=>3, 'temp'=>3),
);

           // prepare one query
           $q = "UPDATE table_name SET temp= CASE id ";

           foreach($sqlInsert as $insert) {
               $q .= "WHEN '".$insert['id']."' THEN ".$insert['temp']." ";
           }

           $q .= "END WHERE id IN (";

           foreach($sqlInsert as $insert) {
                $q .= "'".$insert['id']."', ";
           }
           $q = rtrim($q, ", ");
           $q .= ")";
           
           // Update  
           DB::update(DB::raw($q));

akashvarlani said:

$sqlInsert = array(
   array('id'=>1, 'temp'=>1),
   array('id'=>2, 'temp'=>2),
   array('id'=>3, 'temp'=>3),
);

          // prepare one query
          $q = "UPDATE table_name SET temp= CASE id ";

          foreach($sqlInsert as $insert) {
              $q .= "WHEN '".$insert['id']."' THEN ".$insert['temp']." ";
          }

          $q .= "END WHERE id IN (";

          foreach($sqlInsert as $insert) {
               $q .= "'".$insert['id']."', ";
          }
          $q = rtrim($q, ", ");
          $q .= ")";
          
          // Update  
          DB::update(DB::raw($q));

could you explain your code ? thanks

For mass inserts you can do :

$input = array(....);
$data = [];       // $data is an array of arrays representing a series of row x columns to be inserted

foreach($input as $v) 
{
      $data[] = array(
    'row1' => $v[1], 
    'row2' => $v[2], 
    'row3' => $v[3], 
    'row4' => $v[4], 
    'row5' => $v[5], 
    'row6' => $v[6]);
}

Changes::insert($data);   // Changes is a eloquent model representing a table named 'change'

For mass updates :

Changes::updateOrCreate(array('search_key' => 'search_value'), array('key' => 'value'));