multiple row upsert in wordpress

multiple-row-upsert.php

global $wpdb;

// your table (with a UNIQUE or PRIMARY KEY on the upsert column(s))
$table = $wpdb->prefix . 'my_table';

// prepare your data as an array of rows
$data = [
    [ 'id' => 1, 'col_a' => 'foo',  'col_b' => 'bar' ],
    [ 'id' => 2, 'col_a' => 'baz',  'col_b' => 'qux' ],
    [ 'id' => 3, 'col_a' => 'lorem','col_b' => 'ipsum' ],
];

// build placeholders and flattened values
$place_holders = [];
$flat_values   = [];

foreach ( $data as $row ) {
    // for each row, add "(%d, %s, %s)" and push the values in order
    $place_holders[] = '(%d, %s, %s)';
    $flat_values[]   = $row['id'];
    $flat_values[]   = $row['col_a'];
    $flat_values[]   = $row['col_b'];
}

// columns to insert/update
$columns = [ 'id', 'col_a', 'col_b' ];

// build the ON DUPLICATE KEY UPDATE clause
$updates = [];
foreach ( array_slice( $columns, 1 ) as $col ) {
    // skip the primary key column(s) if you don't want to update them
    $updates[] = "`{$col}` = VALUES(`{$col}`)";
}

// assemble the full SQL
$sql = "
    INSERT INTO `{$table}` (`" . implode( '`,`', $columns ) . "`)
    VALUES " . implode( ',', $place_holders ) . "
    ON DUPLICATE KEY UPDATE " . implode( ', ', $updates );

// prepare and execute
$sql = $wpdb->prepare( $sql, $flat_values );
$wpdb->query( $sql );