PHP MySQL Update Data

Data changes over time -- a user updates their email, an order's status moves from "pending" to "shipped", a product's price changes. The UPDATE statement modifies existing rows in place, changing specific columns to new values, always scoped by a WHERE clause identifying exactly which rows to change.

In this page:

Basic UPDATE with a WHERE Clause
Updating Multiple Columns at Once
Checking How Many Rows Were Updated
Updating a Value Relative to Its Current Value
Updating Multiple Rows That Share a Condition

Basic UPDATE with a WHERE Clause

UPDATE tableName SET column = newValue WHERE condition changes the specified column, only on rows matching the condition -- UPDATE users SET status = inactive WHERE id = 5 changes exactly one user's status, leaving everyone else untouched.

Note: Always pair an UPDATE with a WHERE clause scoped to exactly the rows you intend to change, reviewing it as carefully as you would a DELETE.

Warning: An UPDATE without a WHERE clause changes every single row in the table, which is a devastating mistake if run by accident.

Example: Basic UPDATE with a WHERE Clause

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, status TEXT)");
$db->exec("INSERT INTO users VALUES (5, 'active')");
$db->exec("UPDATE users SET status = 'inactive' WHERE id = 5");
echo "Updated";
?>

Updating Multiple Columns at Once

A single UPDATE statement can change several columns together by separating them with commas: UPDATE users SET name = ?, email = ? WHERE id = ? updates both the name and email of one specific user in one query, rather than running two separate UPDATE statements.

Note: Combine every column that logically changes together (like a full profile edit) into one UPDATE statement, rather than issuing several separate ones.

Warning: Listing a column in SET more than once in the same statement is a syntax error -- each column should appear exactly once per UPDATE.

Example: Updating Multiple Columns at Once

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)");
$db->exec("INSERT INTO users VALUES (1, 'Old', '[email protected]')");
$stmt = $db->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id");
$stmt->bindValue(':name', 'Alice', SQLITE3_TEXT);
$stmt->bindValue(':email', '[email protected]', SQLITE3_TEXT);
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$stmt->execute();
echo "Both columns updated in one query";
?>

Checking How Many Rows Were Updated

mysqli_affected_rows($conn) reports how many rows the most recent UPDATE actually changed -- worth checking to confirm an update matched the expected number of rows, or to detect that an ID did not exist at all (0 rows affected).

Note: Check mysqli_affected_rows() after an UPDATE when it matters to know whether any row was actually changed, especially for confirming a specific record exists.

Warning: MySQL, by default, reports 0 affected rows if an UPDATE matched a row but the new values were identical to the old ones -- this can look like "no row found" when the row was actually found but unchanged.

Example: Checking How Many Rows Were Updated

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, status TEXT)");
$db->exec("INSERT INTO users VALUES (1, 'active')");
$db->exec("UPDATE users SET status = 'inactive' WHERE id = 1");
echo $db->changes() . " row(s) updated"; // mysqli_affected_rows($conn) equivalent
?>

Updating a Value Relative to Its Current Value

An UPDATE can reference a column's own current value within the SET clause, like UPDATE products SET stock = stock - 1 WHERE id = ?, which decreases the existing stock count by one -- useful for counters, running totals, and inventory adjustments without first reading the value into PHP.

Note: Use a relative update (column = column ± value) directly in SQL for counters and running totals, since it happens atomically on the database side without a separate read-then-write round trip.

Warning: Reading a value into PHP, calculating a new value, and writing it back with a separate UPDATE is vulnerable to a race condition if two requests do this concurrently -- an in-SQL relative update avoids that entirely.

Example: Updating a Value Relative to Its Current Value

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (id INTEGER, stock INTEGER)");
$db->exec("INSERT INTO products VALUES (1, 10)");
$db->exec("UPDATE products SET stock = stock - 1 WHERE id = 1");
$result = $db->query("SELECT stock FROM products WHERE id = 1");
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Updating Multiple Rows That Share a Condition

Just like DELETE, an UPDATE's WHERE clause can match more than one row at once -- UPDATE orders SET status = expired WHERE created_at < ? updates every old order in a single statement, far more efficient than looping and updating each row individually.

Note: Prefer one bulk UPDATE matching many rows through its WHERE clause over looping individual UPDATE statements, for the same performance reasons as bulk inserts.

Warning: A bulk UPDATE that unintentionally matches more rows than expected (due to an overly broad WHERE clause) can silently overwrite data on rows you did not mean to touch.

Example: Updating Multiple Rows That Share a Condition

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE orders (id INTEGER, created_at TEXT, status TEXT)");
$db->exec("INSERT INTO orders VALUES (1, '2020-01-01', 'open'), (2, '2024-01-01', 'open')");
$db->exec("UPDATE orders SET status = 'expired' WHERE created_at < '2023-01-01'");
echo $db->changes() . " old order(s) expired";
?>

Common Mistakes

Running UPDATE table SET column = value without a WHERE clause, which updates that column on every single row in the table.
Building an UPDATE's SET or WHERE values from unvalidated, unbound user input, risking SQL injection just as with any other query type.
Updating a row's value based on stale data read earlier in the script, overwriting a change another process may have made in between.

Chapter Summary

UPDATE table SET col = newValue WHERE condition changes only the matching rows' specified columns.
UPDATE without a WHERE clause updates every row in the table -- always double-check a WHERE clause is present.
mysqli_affected_rows($conn) reports how many rows an UPDATE actually changed.

Browser Support

UPDATE is standard SQL and works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Delete Data Next Lesson → PHP MySQL Limit Data