MySQL UPDATE Query

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

In this chapter, we will learn how to use the UPDATE statement in MySQL to modify existing data in a table. The UPDATE statement allows you to update one or more columns in a table based on specific conditions. We will cover the syntax, examples, and important considerations for using the UPDATE statement.

Basic UPDATE Query

To update data in a table, we use the UPDATE statement.

Syntax

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

table_name: The name of the table where you want to update data.
column1, column2, ...: The names of the columns you want to update.
value1, value2, ...: The new values to assign to the columns.
condition: The condition to specify which rows to update.

Example

UPDATE employees SET email = 'rahul.sharma@newemail.com' WHERE first_name = 'Rahul' AND last_name = 'Sharma';

This example updates the email column of the employees table for the row where the first_name is 'Rahul' and the last_name is 'Sharma'.

Updating Multiple Columns

You can update multiple columns in a single UPDATE statement by separating the column assignments with commas.

Example

UPDATE employees SET email = 'priya.singh@newemail.com', hire_date = '2023-08-01' WHERE first_name = 'Priya' AND last_name = 'Singh';

This example updates the email and hire_date columns of the employees table for the row where the first_name is 'Priya' and the last_name is 'Singh'.

Updating All Rows

If you omit the WHERE clause, the UPDATE statement will update all rows in the table.

Example

UPDATE employees SET hire_date = '2023-08-01';

This example updates the hire_date column of all rows in the employees table.

Full Example

Let's go through a full example where we create a table, insert data into it, and perform various UPDATE queries.

Create a Database:

CREATE DATABASE company;

Select the Database:

USE company;

Create a Table:

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

Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

Basic UPDATE Query:

UPDATE employees SET email = 'rahul.sharma@newemail.com' WHERE first_name = 'Rahul' AND last_name = 'Sharma';

UPDATE Multiple Columns:

UPDATE employees SET email = 'priya.singh@newemail.com', hire_date = '2023-08-01' WHERE first_name = 'Priya' AND last_name = 'Singh';

UPDATE All Rows:

UPDATE employees SET hire_date = '2023-08-01';

Query the Data:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@newemail.com	2023-08-01
2	Priya	Singh	priya.singh@newemail.com	2023-08-01
3	Amit	Kumar	amit.kumar@example.com	2023-08-01
4	Neha	Verma	neha.verma@example.com	2023-08-01

Important Considerations

Data Types: Ensure that the values you update match the data types of the columns.
Constraints: Be aware of constraints such as NOT NULL, UNIQUE, and PRIMARY KEY that may affect your ability to update data.
Transactions: Use transactions to ensure data integrity when performing multiple related updates.
Performance: Be cautious when updating large tables, as this can affect performance. Consider using indexing to optimize your queries.

Conclusion

Updating data in a table is a fundamental operation in MySQL. This chapter covered how to use the UPDATE statement to modify existing data in a table, including examples of updating single and multiple columns, as well as updating all rows. In the next chapter, we will learn how to delete data from tables.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

Java Guides

Search This Blog