Updating records with UPDATE

Learn to update an existing record with PDO and a prepared statement.

Updating a record with UPDATE

To update an existing record you run an UPDATE with a prepared statement. The SET clause names the columns to change; WHERE selects which rows are affected.

For example, UPDATE users SET role = ? WHERE name = ? changes the role of a specific user; values are passed via execute().

Without WHERE, UPDATE changes every row in the table; forgetting the clause is dangerous.

Verifying after update

To check whether the update took effect, you fetch the same record with SELECT and print the new value.

PDO fetch(PDO::FETCH_ASSOC) returns a single row as an associative array; if no record matches, it returns false.

This pattern is also useful to compare state before and after the update.

Exercise Update the role and verify

In the skeleton below, use prepare and execute to change the role of 'Alex' to 'admin'. Then fetch the role with SELECT and print it.

Show example solution

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, role TEXT)");
$pdo->exec("INSERT INTO users (name, role) VALUES ('Alex', 'editor')");
$stmt = $pdo->prepare("UPDATE users SET role = ? WHERE name = ?");
$stmt->execute(['admin', 'Alex']);
$stmt = $pdo->prepare("SELECT role FROM users WHERE name = ? ORDER BY id");
$stmt->execute(['Alex']);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['role'] . "\n";

Test yourself

Match UPDATE parts

UPDATE

Table name
SET

New values
WHERE

Which rows
execute()

Runs the statement
What happens if UPDATE has no WHERE?
- The first row is updated
- The query throws an error
- All rows are updated
- No record changes
Show answer

All rows are updated — Without WHERE, UPDATE changes every row in the table.

Complete the UPDATE clause

$stmt = $pdo->prepare("UPDATE users SET role = ? ____ name = ?");

Show answer

$stmt = $pdo->prepare("UPDATE users SET role = ? WHERE name = ?");

Next lesson: Deleting records with DELETE

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies