← Back to PHP Course | Chapter 11: Database | Lesson 20 of 21

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:

  1. Basic UPDATE with a WHERE Clause
  2. Updating Multiple Columns at Once
  3. Checking How Many Rows Were Updated
  4. Updating a Value Relative to Its Current Value
  5. Updating Multiple Rows That Share a Condition
Syntax
php 
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

mysqli_query($conn, $sql);
mysqli_affected_rows($conn);   // how many rows changed

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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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");
// Print "Updated" to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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]')");
// Declare `$stmt`, set to `$db->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id")`
$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();
// Print "Both columns updated in one query" to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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");
// Declare `$result`, set to `$db->query("SELECT stock FROM products WHERE id = 1")`
$result = $db->query("SELECT stock FROM products WHERE id = 1");
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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'");
// Print `$db->changes() . " old order(s) expired"` to the output
echo $db->changes() . " old order(s) expired";
?>
Related Topics

PHP MySQL Delete Data · PHP MySQL Limit Data · PHP MySQL Select Data · PHP MySQL Insert Data

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Running UPDATE table SET column = value without a WHERE clause, which updates that column on every single row in the table.
  2. Building an UPDATE's SET or WHERE values from unvalidated, unbound user input, risking SQL injection just as with any other query type.
  3. 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

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.