update()

The update() method updates existing rows in a table based on specified conditions. It supports prepared statements to ensure SQL injection safety and returns true on success or false on failure.

Signature:

public function update(string $tableName, array $insertData, ?int $numRows = null): bool

Basic Usage – Comparison

$update = [
  'email'      => 'new.email@example.com',
  'status'     => 'active',
  'updated_at' => $db->now()
];

$updatedRows = $db->update('test_users', $update);
$updatedRows = $db->update('test_users', [
  'email'      => 'new.email@example.com',
  'status'     => 'active',
  'updated_at' => $db->now()
]);

Increment/Decrement Example

Use inc() or dec() to safely increase or decrease numeric fields:

$db->where('id', 42);
$updatedRows = $db->update('test_users', [
  'login_count' => $db->inc(),    // increment by 1
  'credit'      => $db->dec(3)    // decrement by 3
]);

Update with Locking (FOR UPDATE)

To ensure atomic updates in concurrent environments, use setQueryOption('FOR UPDATE') together with startTransaction(). This locks the selected row until the transaction is committed or rolled back.

$db->startTransaction();

$db->where('id', 42);
$db->setQueryOption('FOR UPDATE');
$user = $db->getOne('test_users'); // SELECT ... FOR UPDATE

if ($user) {
  $newCredit = $user['credit'] - 5;

  $db->where('id', 42);
  if (!$db->update('test_users', [
    'credit'      => $newCredit,
    'updated_at'  => $db->now()
  ])) {
    $db->rollback();
  } else {
    $db->commit();
  }
} else {
  $db->rollback();
}

Return Value

  • true on success
  • false on failure (check getLastError())

Notes

  • Always use at least one condition to prevent unintended mass updates
  • Supports SQL expressions and special values like $db->now()
  • Prepared statements protect against SQL injection
  • You can use inc() and dec() for numeric adjustments

See Also