What is UPDATE command in SQL and How to use it?

We have learned how to insert data into the SQL databases by using the INSERT command. After inserting data, if you require to change the values in database tables, the UPDATE statement is used for that.

The simple UPDATE statement changes all the records for the specified column. For example:

This UPDATE command will update whole data in the specified column by the new value. For limiting the changes only to the specific records, we use UPDATE statement with the WHERE clause. For example:

The above two UPDATE statements also explain the syntax of using it and summarized below:

  • An UPDATE query is started by the UPDATE keyword.
  • This is followed by the table name that you want to change data for.
  • Then comes the SET keyword.
  • The SET keyword is followed by column name(s). You may specify one or more columns with new values.
  • Optionally and generally, the WHERE clause is used to limit the change in specific rows.

Note that, the UPDATE table command only changes the table data. It does not affect actual table structure. For modifying table structure like column constraints, we use the ALTER TABLE command.

An example of using UPDATE table query

For the examples of UPDATE SQL statement, I am using the sto_employees table. This table has a few columns including employee name, age, salary, join date and phone numbers.

By using UPDATE statement, we will change the records and display the data before and after modification.

In the first example, the salary of an employee is changed by using the UPDATE command. See the query and demo data below:

The data before and after UPDATE:

SQL UPDATE

You can see the second-row value is updated for the emp_salary column.

Updating multiple columns by single query example

As shown in the syntax of the UPDATE command, you may change multiple columns data by using single query. For that, just separate the column names by comma and provide new value for each column as shown in the example below:

The result-sets:

SQL UPDATE multiple

Updating a date column example

Until now we updated the numeric fields by using UPDATE statement. In this query, the date field is updated.

By using UPDATE command, I will update the join_date on an employee ‘Jay’ in our example table.

The query:

The record-sets before and after changing the date column:

SQL UPDATE date

What if we do not use WHERE clause?

If you want to update a column in the whole table then you may omit the WHERE clause. However, this is a point of caution because this generally not required but in certain scenarios may be.

The following query updates the salary of all employees to 4000:

The record-sets:

SQL UPDATE whole

Using the SELECT statement in UPDATE query

As such, the SQL SELECT statement is used to retrieve the records from the tables. You may use SELECT in UPDATE query for providing the new values for the columns, as well as, in the WHERE clause of UPDATE statement.

First, have a look at the usage of providing new values in UPDATE query:

The results of all queries with demo data:

SQL UPDATE SELECT

The first query shows the record of employee ‘Ben’ in sto_employees table. The second query shows complete dummy data of salaries paid to each employee month over month.

Then, an UPDATE query is executed that changed the record of Ben’s salary in the sto_emp_salary_paid table by the value taken from employees table.

In the table graphics, you can see table data after updating the record.

The example of using SELECT in WHERE clause of UPDATE query

Similarly, you may provide the values for the condition(s) in the WHERE clause of the UPDATE query.

In the following example, we will get the employee name from the sto_employees table by providing the ID. On that basis, the salary will be updated in the sto_emp_salary_paid table. See all the queries and record-sets:

The results:

SQL UPDATE SEL 2

The example of using JOIN clause in the UPDATE/SELECT

As using the SELECT statement for providing the value(s) for changing or conditions in the WHERE clause, you may use different valid SELECT statements.

This also includes using the JOIN clause in the SELECT statement. The example below shows using an INNER JOIN clause in the SELECT statement for providing the value to be changed by UPDTAE statement.

The example involves another table, sto_orders to show a demo query: