How to Update Data with SQL?

Welcome back to our SQL tutorial series! So far, we’ve covered how to insert data into your database. In this article, we’re going to explore the UPDATE statement, which allows you to modify existing records in a table. Understanding how to use UPDATE is essential for maintaining and managing your database effectively.

Understanding the UPDATE Statement

The UPDATE statement is used to modify existing records in a table. You can update one or multiple columns at a time. It is important to use the WHERE clause to specify which records should be updated; otherwise, all records in the table will be updated.

Basic Syntax of UPDATE

The basic syntax of the UPDATE statement is as follows:

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

Let’s look at some practical examples to understand how to use the UPDATE statement effectively.

Example Table: employees

We’ll continue to use the employees table for our examples:

Updating a Single Column

To update the salary of an employee with a specific id, you use:

UPDATE employees
SET salary = 75000.00
WHERE id = 1;

This command updates the salary of John Doe (id 1) to $75,000:

Updating Multiple Columns

To update the position and salary of an employee, you use:

UPDATE employees
SET position = 'Senior Software Engineer', salary = 80000.00
WHERE id = 1;

This command updates John Doe’s position and salary:

Updating Multiple Rows

To update the salaries of all employees who are Data Analysts, you use:

UPDATE employees
SET salary = 70000.00
WHERE position = 'Data Analyst';

This command updates the salaries of Jane Smith and Sarah Wilson:

Using UPDATE with Multiple Conditions

You can combine multiple conditions in the WHERE clause using AND or OR. For example, to update the salary of employees who are Software Engineers and have a salary less than $75,000:

UPDATE employees
SET salary = 75000.00
WHERE position = 'Software Engineer' AND salary < 75000.00;

This command updates the salaries of Emily Davis:

Using UPDATE with Subqueries

You can also use subqueries in the SET clause to update data based on other data in the table. For example, to set the salary of employees to the average salary of their position:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees AS e2 WHERE employees.position = e2.position);

This command sets the salary of each employee to the average salary of their position.

Conclusion

The UPDATE statement is a powerful tool for modifying existing records in your SQL database. Whether you need to update a single column, multiple columns, or multiple rows based on specific conditions, understanding how to use UPDATE is essential for effective database management.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!