Skip to content

Instantly share code, notes, and snippets.

@onmotion

onmotion/yii2 batch insert on duplicate update.php

Last active August 8, 2023 06:47
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save onmotion/b31a15506401e6f9395bbb57618d24b0 to your computer and use it in GitHub Desktop.
Save onmotion/b31a15506401e6f9395bbb57618d24b0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
yii2 batch insert on duplicate update.php
<?php
$db = Yii::$app->db;
$sql = $db->queryBuilder->batchInsert($table, $fields, $rows);
$db->createCommand($sql . ' ON DUPLICATE KEY UPDATE')->execute();
@alex-wdmg
Copy link

alex-wdmg commented Apr 27, 2020 •
edited 

  $db = Yii::$app->db;
  $sql = $db->queryBuilder->batchInsert($table, $fields, $rows);
  $count = $db->createCommand($sql . ' ON DUPLICATE KEY UPDATE ' .
      join(', ',
          array_map(function($field) {
              return $field . ' = VALUES(' . $field . ')';
          }, $fields)
      )
  )->execute();

@onmotion
Copy link
Author

$db = Yii::$app->db;
$sql = $db->queryBuilder->batchInsert($table, $fields, $rows);
$count = $db->createCommand($sql . " ON DUPLICATE KEY UPDATE " .
join(", ",
array_map(function($field) {
return $field . ' = VALUES(' . $field . ')';
}, $fields)
)
)->execute();

for such type of DB this example?
For mysql there is already prepared result into $sql

        $db = \Yii::$app->db;
        $sql = $db->queryBuilder->batchInsert('user', ['name', 'age'], [
            ['Tom', 30],
            ['Jane', 20],
            ['Linda', 25],
        ]);
        print_r($sql);

// INSERT INTO "user" ("name", "age") VALUES ('Tom', 30), ('Jane', 20), ('Linda', 25)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment