MySQL UPDATE Row(s) Statement

MySQL UPDATE ROW(s) Statement

The MySQL UPDATE statement is used to modify existing rows in a table. This statement is essential for updating data to reflect changes, corrections, or new information.

Syntax

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

The UPDATE statement has the following components:

  • table_name: The name of the table where the data will be updated.
  • column1, column2, ...: The columns that will be updated.
  • value1, value2, ...: The new values to be assigned to the columns.
  • condition: The condition that specifies which rows to update.

Example MySQL UPDATE ROW(s) Statement

Let's look at some examples of the MySQL UPDATE statement:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a table named employees with columns for id, first_name, last_name, and email.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Smith', 'jane.smith@example.com');

This query inserts two rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Updating a Single Row

Update a single row in the table:

UPDATE employees
SET email = 'john.doe@newdomain.com'
WHERE first_name = 'John' AND last_name = 'Doe';

This query updates the email of the employee named John Doe.

MySQL UPDATE ROW

Step 5: Updating Multiple Rows

Update multiple rows in the table:

UPDATE employees
SET last_name = 'Doe'
WHERE last_name = 'Smith';

This query updates the last name of all employees with the last name Smith to Doe.

MySQL UPDATE MULTIPLE ROWS

Step 6: Verifying the Updates

To verify that the rows have been updated, you can select all rows from the table:

SELECT * 
FROM employees;

This query retrieves all rows from the employees table. The result will show the updated rows.

MySQL SELECT FROM TABLE

Conclusion

The MySQL UPDATE statement is a powerful tool for modifying existing rows in a table. Understanding how to use the UPDATE statement is essential for effective data management and manipulation in MySQL.