MySQL - Update Query

MySQL - The UPDATE Statement

This tutorial covers how to modify existing records in a MySQL table using the UPDATE statement. We will explore its syntax, best practices for safe execution, advanced usage with joins, and how to perform updates from various programming languages.

Understanding the MySQL UPDATE Statement

The MySQL UPDATE statement is used to modify existing records in a table. It is a Data Manipulation Language (DML) command, as it only affects the data within the table, not the table’s structure (like columns or indexes).

Caution is paramount when using UPDATE. If you execute an UPDATE statement without a WHERE clause, all rows in the table will be modified. This can lead to irreversible data loss. The WHERE clause is essential for specifying exactly which row or rows you intend to change.

Syntax

The basic syntax of the UPDATE command is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• You can update one or more columns in a single statement.
• The WHERE clause is used to filter the records to be updated. If omitted, all records are updated.
• The UPDATE statement operates on a single table at a time (unless using a JOIN).

Updating Data: Practical Examples

Let’s set up a sample table to work with. We’ll create a modern customers table with an auto-incrementing primary key, which is a common best practice.

Example Setup

First, create the customers table:

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

Now, insert some sample data. Notice we don’t need to provide the id because it’s set to AUTO_INCREMENT.

INSERT INTO customers (name, age, address, salary)
VALUES
    ('Ramesh', 32, 'Ahmedabad', 2000.00),
    ('Khilan', 25, 'Delhi', 1500.00),
    ('Kaushik', 23, 'Kota', 2000.00),
    ('Chaitali', 25, 'Mumbai', 6500.00),
    ('Hardik', 27, 'Bhopal', 8500.00),
    ('Komal', 22, 'Hyderabad', 4500.00),
    ('Muffy', 24, 'Indore', 10000.00);

Let’s view our data:

SELECT * FROM customers;

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example 1: Updating a Single Record

Let’s update the name of the customer with id 6 to ‘Nikhilesh’.

UPDATE customers
SET name = 'Nikhilesh'
WHERE id = 6;

To verify the change, we select the data again:

SELECT * FROM customers WHERE id = 6;

The output shows the updated name:

id	name	age	address	salary
6	Nikhilesh	22	Hyderabad	4500.00

Example 2: Updating Multiple Records

You can update multiple records that match a condition. Let’s change the address for all customers with an id of 3 or 6 to ‘Visakhapatnam’. A cleaner way to do this is using the IN operator.

UPDATE customers
SET address = 'Visakhapatnam'
WHERE id IN (3, 6);

After running the query, the customers table will look like this:

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Visakhapatnam	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Nikhilesh	22	Visakhapatnam	4500.00
7	Muffy	24	Indore	10000.00

Best Practices for Safe Updates

1. Always Use a WHERE Clause

This is the most important rule. Before running an UPDATE, it’s a great practice to run a SELECT statement with the same WHERE clause to preview which rows will be affected.

-- First, preview the rows to be updated
SELECT * FROM customers WHERE age < 25;

-- If the selection is correct, then run the update
UPDATE customers SET salary = salary * 1.05 WHERE age < 25;

2. Use Transactions

For critical updates, wrap your statement in a transaction. This allows you to undo the changes if something goes wrong. This is supported by storage engines like InnoDB (the default in modern MySQL).

START TRANSACTION;

-- Perform the update
UPDATE customers SET address = 'Unknown' WHERE id = 5;

-- Check the result
SELECT * FROM customers WHERE id = 5;

-- If the change is correct, make it permanent
-- COMMIT;

-- If something is wrong, undo the change
-- ROLLBACK;

3. Be Aware of Safe Update Mode

Many MySQL clients enable “Safe Update Mode” (sql_safe_updates) by default. This mode prevents UPDATE or DELETE statements that don’t use a key column in the WHERE clause or a LIMIT clause. If you get an error about safe updates, your query is likely too broad and potentially dangerous. To proceed, you must either refine your WHERE clause to use an indexed key or (if you are certain) temporarily disable the mode for your session with SET SQL_SAFE_UPDATES = 0;.

Updating from a Client Program (Modern Approach)

When updating data from an application (like PHP, Node.js, Java, or Python), the most critical best practice is to use prepared statements (or parameterized queries). This technique prevents SQL injection, a common and serious security vulnerability.

The following examples demonstrate the modern, secure way to perform an `UPDATE` operation.

PHP (using PDO)

PHP Data Objects (PDO) is the modern, recommended way to interact with databases in PHP. It provides a consistent, object-oriented API and helps prevent SQL injection using prepared statements.

<?php
$host = 'localhost';
$db   = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$newAddress = 'Pune';
$customerId = 4;

$sql = "UPDATE customers SET address = ? WHERE id = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([$newAddress, $customerId]);

$affectedRows = $stmt->rowCount();
echo "Update successful. {$affectedRows} row(s) affected.";
?>

Node.js (using mysql2/promise and async/await)

Using mysql2/promise with async/await syntax and connection pools provides a modern, readable, and efficient way to handle database operations in Node.js.

const mysql = require('mysql2/promise');

async function main() {
    const pool = mysql.createPool({
        host: 'localhost',
        user: 'your_username',
        password: 'your_password',
        database: 'your_database',
        waitForConnections: true,
        connectionLimit: 10,
        queueLimit: 0
    });

    const newSalary = 9000.00;
    const customerId = 5;

    const sql = 'UPDATE customers SET salary = ? WHERE id = ?';

    try {
        const [result] = await pool.execute(sql, [newSalary, customerId]);
        console.log(`Update successful. ${result.affectedRows} row(s) changed.`);
    } catch (error) {
        console.error('Failed to update record:', error);
    } finally {
        await pool.end(); // Close all connections in the pool
    }
}

main();

Java (using JDBC with try-with-resources)

Modern Java uses try-with-resources to automatically manage resources like connections and statements, preventing resource leaks. PreparedStatement is used for security.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateRecord {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        String sql = "UPDATE customers SET age = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, 26); // Set the new age
            pstmt.setInt(2, 2);  // Set the customer ID

            int affectedRows = pstmt.executeUpdate();

            System.out.println("Update successful. " + affectedRows + " row(s) affected.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Python (using mysql-connector-python with with)

Python’s with statement ensures that resources are properly managed. The connector uses execute() with a tuple of parameters to prevent SQL injection.

import mysql.connector
from mysql.connector import errorcode

def update_customer():
    config = {
        'user': 'your_username',
        'password': 'your_password',
        'host': 'localhost',
        'database': 'your_database'
    }

    sql = "UPDATE customers SET salary = %s WHERE id = %s"
    data = (7000.00, 4) # New salary and customer ID

    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor() as cursor:
                cursor.execute(sql, data)
                conn.commit() # Important: commit the transaction
                print(f"Update successful. {cursor.rowcount} row(s) affected.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")

if __name__ == '__main__':
    update_customer()