SQL UPDATE Query

Introduction

In this chapter, we will learn how to use the UPDATE statement in SQL. The UPDATE statement is used to modify the existing records in a table. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the UPDATE statement effectively.

What is the UPDATE Statement?

The UPDATE statement is used to change the data of one or more records in a table. It can update all rows, specific rows based on a condition, or specific columns in the table.

Syntax for UPDATE

Basic Syntax

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

• table_name: The name of the table you want to update.
• column1, column2, ...: The columns you want to update.
• value1, value2, ...: The new values for the specified columns.
• condition: The condition that must be met for a row to be updated (optional).

Example

Assume we have a table named employees:

UPDATE employees
SET email = 'new.email@example.com'
WHERE id = 1;

This command updates the email of the employee with id 1.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 50000),
('Sita', 'Patel', 'sita.patel@example.com', 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 50000);

3. Update a Specific Row

To update the email of the employee with id 1:

UPDATE employees
SET email = 'ramesh.new@example.com'
WHERE id = 1;

4. Verify the Update

SELECT * FROM employees
WHERE id = 1;

Output

id	first_name	last_name	email	salary
1	Ramesh	Kumar	ramesh.new@example.com	50000

5. Update Multiple Columns

To update the email and salary of the employee with id 2:

UPDATE employees
SET email = 'sita.new@example.com', salary = 65000
WHERE id = 2;

6. Verify the Update

SELECT * FROM employees
WHERE id = 2;

Output

id	first_name	last_name	email	salary
2	Sita	Patel	sita.new@example.com	65000

7. Update Multiple Rows

To give all employees with the last name 'Kumar' a raise:

UPDATE employees
SET salary = salary + 5000
WHERE last_name = 'Kumar';

8. Verify the Update

SELECT * FROM employees
WHERE last_name = 'Kumar';

Output

id	first_name	last_name	email	salary
1	Ramesh	Kumar	ramesh.new@example.com	55000
5	Ramesh	Kumar	ramesh.kumar2@example.com	55000

Using UPDATE Without a WHERE Clause

Using the UPDATE statement without a WHERE clause will update all rows in the table.

Example

To set the salary of all employees to 60000:

UPDATE employees
SET salary = 60000;

Verify the Update

SELECT * FROM employees;

Output

id	first_name	last_name	email	salary
1	Ramesh	Kumar	ramesh.new@example.com	60000
2	Sita	Patel	sita.new@example.com	60000
3	Arjun	Singh	arjun.singh@example.com	60000
4	Priya	Sharma	priya.sharma@example.com	60000
5	Ramesh	Kumar	ramesh.kumar2@example.com	60000

Conclusion

The UPDATE statement is used for modifying existing records in a table. This chapter covered the basic syntax, updating specific rows, updating multiple columns, and updating all rows in a table. Understanding how to use the UPDATE statement effectively will enhance your ability to manage and manipulate your database data. In the next chapter, we will explore how to delete data using the DELETE statement.