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.
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.Let's look at some examples of the MySQL UPDATE
statement:
USE mydatabase;
This query sets the context to the database named mydatabase
.
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
.
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.
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.
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.
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.
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.