Update Data

Updating Existing Database Records (UPDATE...SET)

The UPDATE statement modifies existing record column values in a database table matching specified WHERE conditions.

Update Operation Pipeline

flowchart LR
    A["Prepare UPDATE Query"] -->|'UPDATE users SET email = :email WHERE id = :id'|| B["Bind Parameters"]
    B -->|'$stmt->execute()'|| C["MySQL Modifies Matching Record Row"]
    C --> D["Check $stmt->rowCount() for Affected Row Count"]

Practical Code Example

<?php
declare(strict_types=1);

function updateUserEmail(PDO $pdo, int $userId, string $newEmail): bool
{
    // Prepared UPDATE query
    $sql = "UPDATE users SET email = :email, updated_at = NOW() WHERE id = :id";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        'email' => $newEmail,
        'id' => $userId
    ]);

    $affectedRows = $stmt->rowCount();
    echo "Updated $affectedRows record(s).
";

    return $affectedRows > 0;
}

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    updateUserEmail($pdo, 1, "[email protected]");

} catch (PDOException $e) {
    die("Update Failed: " . $e->getMessage());
}

Best Practices

• Always Include WHERE Clause: Omitting WHERE in an UPDATE statement overwrites that column for EVERY row in the database table!
• Update Timestamp Columns: Maintain an updated_at column to track when records were modified.
• Use Transactions for Multi-Table Updates: Ensure multi-table database updates complete atomically.

Self-Check Challenge

Write a SQL UPDATE statement using named parameters to set status = :status for a specific record :id!