2.4.5. UPDATE

2.4.5.1. Building The Query

2.4.5.1.1. Table

Use the table() method to specify the table to update.

$update->table('foo');

2.4.5.1.2. Columns

You can set a named placeholder and its corresponding bound value using the column() method.

// UPDATE foo SET bar = :bar
$update->column('bar', $bar_value);

Note that the PDO parameter type will automatically be set for strings, integers, floats, and nulls. If you want to set a PDO parameter type yourself, pass it as an optional third parameter.

// UPDATE foo SET bar = :bar
$update->column('bar', $bar_value, \PDO::PARAM_LOB);

You can set several placeholders and their corresponding values all at once by using the columns() method:

// UPDATE foo SET bar = :bar, baz = :baz
$update->columns([
    'bar' => $bar_value,
    'baz' => $baz_value
]);

However, you will not be able to specify a particular PDO parameter type when doing do.

Bound values are automatically quoted and escaped; in some cases, this will be inappropriate, so you can use the raw() method to set column to an unquoted and unescaped expression.

// UPDATE foo SET bar = NOW()
$update->raw('bar', 'NOW()');

2.4.5.1.3. WHERE

(All WHERE methods support implicit and sprintf() inline value binding.)

The Update WHERE methods work just like their equivalent Select methods:

  • where() and andWhere() AND a WHERE condition
  • orWhere() ORs a WHERE condition
  • catWhere() concatenates onto the end of the most-recent WHERE condition
  • whereSprintf() and andWhereSprintf() AND a WHERE condition with sprintf()
  • orWhereSprintf() ORs a WHERE condition with sprintf()
  • catWhereSprintf() concatenates onto the end of the most-recent WHERE condition with sprintf()

2.4.5.1.4. ORDER BY

Some databases (notably MySQL) recognize an ORDER BY clause. You can add one to the Update with the orderBy() method; pass each expression as a variadic argument.

// UPDATE ... ORDER BY foo, bar, baz
$update
    ->orderBy('foo')
    ->orderBy('bar', 'baz');

2.4.5.1.5. LIMIT and OFFSET

Some databases (notably MySQL and SQLite) recognize a LIMIT clause; others (notably SQLite) recognize an additional OFFSET. You can add these to the Update with the limit() and offset() methods:

// LIMIT 10 OFFSET 40
$update
    ->limit(10)
    ->offset(40);

2.4.5.1.6. RETURNING

Some databases (notably PostgreSQL) recognize a RETURNING clause. You can add one to the Update using the returning() method, specifying columns as variadic arguments.

// UPDATE ... RETURNING foo, bar, baz
$update
    ->returning('foo')
    ->returning('bar', 'baz');

2.4.5.1.7. Flags

You can set flags recognized by your database server using the setFlag() method. For example, you can set a MySQL LOW_PRIORITY flag like so:

// UPDATE LOW_PRIORITY foo SET bar = :bar WHERE baz = :__1__
$update
    ->table('foo')
    ->column('bar', $bar_value)
    ->where('baz = ', $baz_value)
    ->setFlag('LOW_PRIORITY');

2.4.5.2. Performing The Query

Once you have built the query, call the perform() method to execute it and get back a PDOStatement.

$pdoStatement = $update->perform();

If you added a RETURNING clause with the returning() method, you can retrieve those column values with the returned PDOStatement:

$pdoStatement = $update->perform();
$values = $pdoStatement->fetch(); // : array