Updating Existing Data Using the UPDATE Statement

In SQL, the UPDATE statement is used to modify existing data in a table. It allows you to update specific columns of one or multiple rows based on specified conditions.

Table of Contents

Basic Syntax

The basic syntax of the INSERT statement is:

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

table_name: Specifies the name of the table where data will be updated.
column1, column2, …: Specifies the columns to be updated.
value1, value2, …: Specifies the new values to be assigned to the columns.
WHERE condition: Specifies the condition that determines which rows will be updated.

Updating Specific Columns

To update specific columns in a table, you need to provide the column names and their corresponding new values in the SET clause.

The query bellow updates the “salary” and “department_id” columns of the “employees” table for the employee with an “employee_id” of 123.

UPDATE employees
SET salary = 55000, department_id = 2
WHERE employee_id = 123;

Updating Multiple Rows

The UPDATE statement can update multiple rows based on a specific condition.

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 1;

This query updates the “salary” column of the “employees” table, increasing it by 5% for all employees in the department with a “department_id” of 1.

Using Subqueries

You can also use subqueries within the UPDATE statement to update data based on the results of a subquery.

UPDATE employees
SET department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
)
WHERE employee_id = 123;

This query updates the “department_id” column of the “employees” table for the employee with an “employee_id” of 123, using the department ID obtained from the “departments” table based on the department name ‘Sales’.

Updating with JOINs

In more complex scenarios, you can use JOINs in the UPDATE statement to update data based on related tables.

UPDATE employees
JOIN departments ON employees.department_id = departments.department_id
SET employees.salary = employees.salary * 1.05
WHERE departments.location = 'New York';

This query updates the “salary” column of the “employees” table, increasing it by 5%, for all employees in the department located in ‘New York’.

By using the UPDATE statement, you can modify existing data in your tables. Whether you’re updating specific columns, multiple rows, using subqueries, or JOINs, the UPDATE statement provides a powerful tool for managing and updating data in your database.