Automattic\WooCommerce\Internal\Utilities

DatabaseUtil::insert_on_duplicate_key_update │ public │ WC 1.0

Hybrid of $wpdb->update and $wpdb->insert. It will try to update a row, and if it doesn't exist, it will insert it. This needs unique constraints to be set on the table on all ID columns.

You can use this function only when:

  1. There is only one unique constraint on the table. The constraint can contain multiple columns, but it must be the only one unique constraint.
  2. The complete unique constraint must be part of the $data array.
  3. You do not need the LAST_INSERT_ID() value.

Method of the class: DatabaseUtil{}

No Hooks.

Returns

Int. Returns the value of DB's ON DUPLICATE KEY UPDATE clause.

Usage

$DatabaseUtil = new DatabaseUtil();
$DatabaseUtil->insert_on_duplicate_key_update( $table_name, $data, $format ): int;
$table_name(string) (required)
Table name.
$data(array) (required)
Unescaped data to update (in column => value pairs).
$format(array) (required)
An array of formats to be mapped to each of the values in $data.

DatabaseUtil::insert_on_duplicate_key_update() code WC 10.5.0

woocommerce/src/Internal/Utilities/DatabaseUtil.php 
public function insert_on_duplicate_key_update( $table_name, $data, $format ): int {
	global $wpdb;
	if ( empty( $data ) ) {
		return 0;
	}

	$columns      = array_keys( $data );
	$value_format = array();
	$values       = array();
	$index        = 0;
	// Directly use NULL for placeholder if the value is NULL, since otherwise $wpdb->prepare will convert it to empty string.
	foreach ( $data as $key => $value ) {
		if ( is_null( $value ) ) {
			$value_format[] = 'NULL';
		} else {
			$values[]       = $value;
			$value_format[] = $format[ $index ];
		}
		++$index;
	}
	$column_clause       = '`' . implode( '`, `', $columns ) . '`';
	$value_format_clause = implode( ', ', $value_format );
	$on_duplicate_clause = $this->generate_on_duplicate_statement_clause( $columns );
	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Values are escaped in $wpdb->prepare.
	$sql = $wpdb->prepare(
		"
INSERT INTO $table_name ( $column_clause )
VALUES ( $value_format_clause )
$on_duplicate_clause
",
		$values
	);
	// phpcs:enable
	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared.
	return $wpdb->query( $sql );
}