WireDatabaseDialect::upsert()

Get an INSERT statement that updates the existing row when it would violate a unique key

On MySQL this is INSERT ... ON DUPLICATE KEY UPDATE; other databases have their own syntax, which may need to know the key that the insert conflicts on (the conflict option). Values are given as SQL expressions, most often bound parameter names, so bind them to the prepared statement as you would for any other query.

Available since version 3.0.273.

Example

$sql = $database->dialect()->upsert('caches',
  ['name', 'data', 'expires'], // insert these columns, bound as :name, :data, :expires
  ['data', 'expires'], // when the row exists, update these columns to the inserted values
  ['conflict' => ['name']] // caches.name is the primary key that the insert conflicts on
);
$query = $database->prepare($sql);
$query->bindValue(':name', $name);
$query->bindValue(':data', $data);
$query->bindValue(':expires', $expires);
$query->execute();

// custom expressions for inserted values and updates
$sql = $dialect->upsert('sessions',
  ['id' => ':id', 'data' => ':data', 'ts' => 'NOW()'],
  ['data', 'ts' => 'NOW()'],
  ['conflict' => ['id']]
);

// multiple rows of values: numbers are inserted as given, strings are quoted, null is NULL
$sql = $dialect->upsert('pages', ['id', 'sort'], ['sort'], [
  'conflict' => ['id'],
  'rows' => <a href='/api/ref/1001-0-1002-1-1003-2/'>1001, 0], [1002, 1], [1003, 2</a>,
]);
$database->exec($sql);

Usage

// basic usage
$string = $wireDatabaseDialect->upsert(string $table, array $columns, array $update);

// usage with all arguments
$string = $wireDatabaseDialect->upsert(string $table, array $columns, array $update, array $options = []);

Arguments

NameType(s)Description
$tablestring

Table name

$columnsarray

Columns to insert: a list of column names, each bound as :column, and/or column => expression where expression is a SQL value expression (i.e. ':bindName', 'NULL', 'NOW()').

$updatearray

Columns to update when the row already exists: a list of column names, each set to the value that would have been inserted (the column must be among $columns), and/or column => expression for another SQL expression (i.e. 'NOW()', 'qty+1', ':bindName').

$options (optional)array
  • conflict (array): Column names of the primary or unique key that the insert conflicts on. Not needed by MySQL, optional for SQLite, required by databases that cannot infer it (i.e. PostgreSQL).
  • rows (array): Insert multiple rows of scalar VALUES (not expressions): each row is a list in the same order as $columns (i.e. <a href='/api/ref/1-5-2-6/'>1, 5], [2, 6</a>). Integers and floats are inserted as given, strings are quoted, booleans become 1/0 and null becomes NULL; anything else throws. When present, expressions given in $columns are not used. Use bound parameters instead when inserting one row.

Return value

string

Exceptions

Method can throw exceptions on error:

  • WireDatabaseException

WireDatabaseDialect methods and properties

API reference based on ProcessWire core version 3.0.275