SQL Update Statement

The SQL UPDATE statement is used to modify existing records in a table. It’s a crucial statement in SQL and is used extensively in database management. With the UPDATE statement, you can change the values of one or more columns in a table for a specific set of records.

You’ll need to use the WHERE clause to filter your query results. This is where the SQL WHERE with AND, OR, NOT Operators come into play. Additionally, you can also use the SQL INSERT INTO statement to insert new rows of data into a table.

SQL Update Syntax

The SQL UPDATE statement is used to modify existing data in a table. It allows you to update one or more columns in a table based on specified conditions. The basic syntax of the UPDATE statement is as follows:

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

The UPDATE statement consists of three main parts:

• The table_name specifies the name of the table that you want to update.
• The SET clause specifies the columns that you want to update and the new values that you want to set for those columns.
• The WHERE clause specifies the conditions that must be met for the rows to be updated. If you omit the WHERE clause, all rows in the table will be updated.

SQL Update Example

Let’s consider the following example. Suppose we have a table called employees with the following data:

We want to update the salary of the employee with id 2 to 65000. Here’s how we can do it using the UPDATE statement:

UPDATE employees
SET salary = 65000
WHERE id = 2;

After executing this statement, the data in the employees table will look like this:

SQL Update with Multiple Columns

You can update multiple columns in a table using the UPDATE statement. Here’s an example:

UPDATE employees
SET salary = 75000, last_name = 'Jones'
WHERE id = 3;

After executing this statement, the data in the employees table will look like this:

SQL Update with Subqueries

You can also use subqueries in the SET clause of the UPDATE statement to update data based on the results of a subquery. Here’s an example:

UPDATE employees
SET salary = (SELECT MAX(salary) FROM employees)
WHERE id = 1;

After executing this statement, the data in the employees table will look like this:

In addition to the basic syntax of the UPDATE statement, there are also some important considerations to keep in mind when using this statement in practice. Let’s take a look at some of them below.

Updating with EXISTS Clause

In some cases, we may want to update a table based on whether a certain condition is met in another table. We can do this using the EXISTS clause in the WHERE clause of our UPDATE statement. For example, let’s say we have a “employees” table and a “managers” table, and we want to update the “employees” table to set their “manager_id” to the ID of their corresponding manager in the “managers” table. We could do this with the following SQL:

UPDATE employees
SET manager_id = managers.manager_id
FROM managers
WHERE EXISTS (
  SELECT 1
  FROM managers
  WHERE managers.manager_id = employees.employee_id
);

Updating with JOIN Clause

Similarly, we can also update a table using data from another table by using the JOIN clause in our UPDATE statement. For example, let’s say we have a “employees” table and a “departments” table, and we want to update the “employees” table to set their “department_id” to the ID of their corresponding department in the “departments” table. We could do this with the following SQL:

UPDATE employees
SET department_id = departments.department_id
FROM employees
JOIN departments ON employees.department_name = departments.department_name;

In this example, we are joining the “employees” table with the “departments” table on the “department_name” column, and updating the “department_id” column in the “employees” table with the corresponding value from the “departments” table.

Updating with CASE Statement

In addition to using simple expressions and subqueries in the SET clause of the UPDATE statement, you can also use the CASE statement to update data based on a conditional statement. The CASE statement evaluates a list of conditions and returns a result when the first condition is met.

Here’s an example of updating data based on a conditional statement using the CASE statement:

UPDATE employees
SET salary = CASE 
              WHEN age <= 30 THEN salary + 5000
              WHEN age > 30 AND age <= 40 THEN salary + 3000
              ELSE salary + 1000
            END;

In this example, the salary of employees is updated based on their age. If the age is less than or equal to 30, their salary is increased by 5000. If the age is between 31 and 40, their salary is increased by 3000. Otherwise, their salary is increased by 1000.

Limiting the Number of Rows to be Updated

If you want to update a limited number of rows, you can use the LIMIT clause to specify the maximum number of rows to be updated. This can be useful when updating a large table to avoid overwhelming system resources.

Here’s an example of updating a limited number of rows:

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'sales'
LIMIT 100;

In this example, the salaries of employees in the ‘sales’ department are increased by 10%. Only the first 100 employees in the ‘sales’ department will be updated, as specified by the LIMIT clause.

Tips and Best Practices

When using the UPDATE statement, it’s important to follow some best practices to avoid unintended consequences. Here are some tips to keep in mind:

• Always use a WHERE clause to specify the rows to be updated. Without a WHERE clause, all rows in the table will be updated.
• Test the UPDATE statement on a small subset of the data before running it on the entire table.
• Take a backup of the data before updating critical data to avoid irreversible damage.
• Use transaction blocks to ensure atomicity and consistency of the data.

Beginner

Customers Table:

Update the name of a customer in the “Customers” table where the customer ID is 2.

Intermediate

Orders Table:

Update the “Order_Status” column in the “Orders” table to “Completed” for all orders made by a particular customer where the customer ID is specified.

Advanced

Order_Details Table:

Use a JOIN statement to update the “Order_Total” column in the “Orders” table to the sum of the total amounts in the “Order_Details” table where the order IDs match.

Conclusion

The SQL UPDATE statement is a key tool for modifying data in your database tables. Whether you need to make small changes to a single record or update a large batch of data, the UPDATE statement can help you get the job done. In this article, we’ll explore the syntax and examples of the UPDATE statement, as well as advanced techniques like subqueries and joins. To learn more about SQL data manipulation, check out our articles on SQL DELETE and the SQL MIN, MAX, COUNT, AVG, and SUM statements.