SQL UPDATE Statement

The SQL UPDATE statement can be used to update records on a database table as mentioned below.

  • Update single column on all or specific set of rows of a table.
  • Update multiple columns on all or a specific set of rows of a table.

In the previous chapter, we discussed how to insert and select data from a table.

Now, let's see how to update the records using the UPDATE statement.

Syntax

Here is the basic syntax of an UPDATE statement.

  • The table_name represents the table name.
  • The column1_name, column2_name, and so on. represents the column names.
  • The value1, value2, and so on. represents the new values to the respective columns for the update.
  • The condition represents the condition to filter records on the table for the update, which can be a combination of multiple conditions combined using logical operators.
UPDATE table_name
SET column1_name = value1, column2_name = value2, ...
WHERE condition;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

UPDATE With WHERE Clause

The below SQL statement updates the salary to 50000 for an employee with employee_id 3.

Run this on IDE

UPDATE employees
SET salary = 50000
WHERE employee_id = 3;

After successful execution, use the SELECT statement to fetch the records from the table, which shows the updated data.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 50000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

UPDATE Without WHERE Clause

The below SQL statement updates the salary to 50000 for employees, as it doesn't include a WHERE clause.

  • Without a WHERE clause, the query fetches all the records for the update.

Run this on IDE

UPDATE employees
SET salary = 50000;

After successful execution, use the SELECT statement to fetch the records from the table, which shows the updated data.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 50000
2 Tony Blank M 1982-08-07 2010-01-05 50000
3 Andrew Russel M 1998-05-04 2012-02-20 50000
4 James Cooper M 2000-10-20 2015-05-10 50000
5 Rose Cutler F 1985-08-08 2015-06-21 50000

UPDATE Multiple Columns

The below SQL statement updates the name and salary of an employee with employee_id 3.

Run this on IDE

UPDATE employees
SET salary = 60000, employee_name = 'Andrew Cooper'
WHERE employee_id = 3;

After successful execution, use the SELECT statement to fetch the records from the table, which shows the updated data.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Cooper M 1998-05-04 2012-02-20 60000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Tips & Suggestions

Always make sure to use the WHERE clause on the UPDATE statement to avoid updating all the records of a table.

Always be specific while updating table records.

Overall

We now know how to update records on a table using the UPDATE statement.

Related Links