MySQL Update Data

La Lakshmi Priya Updated 04 Nov 2026

6 min read ·Lesson 222 of 248

MySQL Update Data Changing Existing Rows In simple terms: Data changes all the time: a customer moves to another city, an order is paid, a product's stock goes down when it is sold. The SQL UPDATE statement changes values in existing rows, and the WHERE clause decides which rows are changed.

Technically: UPDATE table SET column = value, ... WHERE condition modifies every row matching the condition. Values can be constants, parameters, or expressions based on the current values (stock = stock - 1). After execution, affected_rows reports how many rows were actually changed. Like DELETE, an UPDATE without WHERE affects every row.

Syntax UPDATE customers SET city = ? WHERE id = ?; Warning: UPDATE products SET price = 10; changes the price of every product. Always include a precise WHERE clause.

A Basic Update <?php require DIR . '/src/db.php';

$db = db();

$db->execute_query('UPDATE customers SET city = ? WHERE id = ?', ['Puducherry', 4]); echo "Changed rows: {$db->affected_rows}\n";

$customer = $db->execute_query('SELECT name, city FROM customers WHERE id = ?', [4])->fetch_assoc(); echo "{$customer['name']} now lives in {$customer['city']}\n"; Output Changed rows: 1 Changed rows: 0 Dev D'Souza now lives in Puducherry affected_rows Counts Changed Rows The second update found the row, but the city was already Puducherry, so nothing changed and affected_rows is 0. This matters when interpreting results:

affected_rows Meaning

0 That many rows were changed 0 No row matched or matching rows already had the new values To distinguish "not found" from "no change", mysqli::$info shows both numbers after an UPDATE sent with query():

<?php $db->query("UPDATE customers SET city = 'Puducherry' WHERE id = 4"); echo $db->info . "\n"; Output Rows matched: 1 Changed: 0 Warnings: 0 Updating Several Columns <?php require DIR . '/src/db.php';

function updateCustomerProfile(mysqli $db, int $id, array $input): bool { $editable = ['name', 'city']; // allow-list of columns users may change $sets = []; $params = [];

foreach ($editable as $column) { if (array_key_exists($column, $input)) { $sets[] = "$column = ?"; $params[] = $input[$column] === '' ? null : trim((string) $input[$column]); } }

if ($sets === []) { return false; }

$params[] = $id; $db->execute_query('UPDATE customers SET ' . implode(', ', $sets) . ' WHERE id = ?', $params);

return $db->affected_rows > 0; }

var_dump(updateCustomerProfile(db(), 5, ['name' => 'Elango R', 'city' => 'Tiruchirappalli', 'email' => 'hacker@example.com'])); Output bool(true) How It Works Only columns in the $editable allow-list can be updated. The email field sent along with the form is ignored, so users cannot change fields they are not supposed to (for example, is_admin or email without verification). This prevents mass assignment vulnerabilities. Column names in the SQL come only from the allow-list; all values are parameters. Empty strings become NULL for optional fields. The customer's ID is the last parameter, matching the final ? in WHERE id = ?. Relative Updates: Let the Database Calculate To change a value based on its current value — reducing stock, increasing a view counter — compute it inside the SQL statement:

UPDATE products SET stock = stock - 2 WHERE sku = 'LAMP-01'; Reading the value into PHP, calculating, and writing it back is a race condition: two simultaneous requests can both read 8 and both write 6, losing one sale. An update like stock = stock - ? is performed atomically by the database.

Preventing Overselling Adding a condition to the WHERE clause makes the update succeed only if enough stock is available:

<?php require DIR . '/src/db.php';

function reserveStock(mysqli $db, string $sku, int $quantity): string { $db->execute_query( 'UPDATE products SET stock = stock - ? WHERE sku = ? AND stock >= ?', [$quantity, $sku, $quantity] );

if ($db->affected_rows === 1) { $left = $db->execute_query('SELECT stock FROM products WHERE sku = ?', [$sku])->fetch_column(); return "Reserved $quantity × $sku ($left left)"; }

return "Cannot reserve $quantity × $sku: not enough stock"; }

$db = db();

echo reserveStock($db, 'LAMP-01', 2) . "\n"; echo reserveStock($db, 'BAG-02', 1) . "\n"; echo reserveStock($db, 'LAMP-01', 10) . "\n"; Output Reserved 2 × LAMP-01 (6 left) Cannot reserve 1 × BAG-02: not enough stock Cannot reserve 10 × LAMP-01: not enough stock Why This Is Safe Checking and decrementing happen in one atomic statement; no other request can change the stock in between. If the stock is insufficient, the WHERE condition matches no row and nothing changes (affected_rows = 0). Two customers buying the last Desk Lamp at the same moment cannot both succeed: MySQL locks the row during the update, so the second update sees the new stock. (After this example, the Desk Lamp's stock is 6.)

Updating a Status Only From an Expected State The same idea protects workflows: an order should only become "paid" if it is currently "pending":

<?php require DIR . '/src/db.php';

$db = db();

$db->execute_query("UPDATE orders SET status = 'paid' WHERE id = ? AND status = 'pending'", [501]); echo $db->affected_rows === 1 ? "Order 501 marked as paid\n" : "Order 501 was not pending\n";

$db->execute_query("UPDATE orders SET status = 'paid' WHERE id = ? AND status = 'pending'", [501]); echo $db->affected_rows === 1 ? "Order 501 marked as paid\n" : "Order 501 was not pending\n"; Output Order 501 marked as paid Order 501 was not pending If a payment confirmation arrives twice (payment providers often retry webhooks), the second update changes nothing, and the application can avoid sending a second confirmation email.

Automatic Timestamps The products.updated_at column was declared with ON UPDATE CURRENT_TIMESTAMP (see MySQL Create Table), so MySQL updates it automatically whenever a product row actually changes. No PHP code is needed — and it cannot be forgotten.

Updates That Belong Together When an operation updates several rows or tables — reducing stock for every item in an order and marking the order as paid — wrap the updates in a transaction, so they succeed or fail together (see PDO Transactions).

Common Mistakes Missing WHERE Clause Effect: Every row is changed.

Fix: Always write a precise WHERE clause; test it with a SELECT first.

Read-Modify-Write in PHP Effect: Lost updates under concurrent requests.

Fix: Use relative updates (stock = stock - ?) and conditions in WHERE.

Updating Columns Chosen by the User Effect: Mass assignment — users change fields they should not.

Fix: Allow-list editable columns.

Misreading affected_rows Effect: "No change" is mistaken for "not found".

Fix: Check existence separately, or inspect $db->info.

Best Practices Update with prepared statements and precise WHERE clauses. Let the database perform calculations atomically. Add state conditions (AND stock >= ?, AND status = 'pending') to enforce business rules safely. Allow-list the columns that can be updated from user input. Group related updates in transactions. Official reference: https://www.php.net/manual/en/mysqli.query.php