Update statement of SQL

The Update statement of the SQL is used to update records in the database tables. The update SQL statement updates existing data in a table.

With a single update query, you can update a single column of one row, multiple columns data of the same row or multiple columns data in multiple rows, as per requirement of your project or scenario.

This Update command tutorial will show you how to update single column data and multiple columns of the single row. Also, you will see the example of updating multiple columns and rows with single update query.

A few update statement examples

Following are a few online examples that we will use in this tutorial:

SQL update without where clause example

Update with set example with where clause

Syntax of SQL Update statement with SET command

The general syntax of the Update is:

UPDATE table_name

SET col1=val1, col2=val2, col3=val3,….

Where col=val AND/OR col=val;

So the update command is followed by the table name, that needs to be updated.After that the Set keyword is followed by the column name(s) with the new value(s) separated by commas. If you simply use update query without the Where clause, the whole table data of specified columns will be changed.

In order to update only specific data, for example when you update the address in your email account then only your address should be changed. As such, the email_accounts table may contain all email addresses of the account holders.

In order to change only specific data, you have to use the Where clause in update SQL query. Let us take a same example, updating your address in your account, this is how the update command will look:

1 2 3 4 5

Update tbl_email_accoutns   Set address = ‘new address’   Where email_address=’your_address@email.com’

As your email should be unique in a table so it will only update the single record.

Now let us show you examples of the update command with our created database and table. We will use same database, test_db and tbl_employee table to show a few examples of the update with SET and Where clause.

Just to remind you, this is how our example table is created in test_db:

Now as we have a table tbl_employee with five columns (emp_id, emp_name, emp_age, emp_salary and joining_date). Also, we have some data by using insert statement. For update table demo, we have five rows of data in our table.

See Demo Table Online

Update example to alter whole column’s data

This example shows how to update table data of emp_salary column without using the Where clause in update command.

The table data will look like this after executing this update query:

See Demo Table Online

As you can see in the updated table, all columns are modified with 4000 in the emp_salary column. Practically, you would not want that. As you may only need to modify the salary of one employee, for example, Ben’s from 3700 to 4000.

The example below shows how you can change just Ben’s data by using the Where clause.

Update / set example with Where clause

This example shows how to update only specific row’s data of emp_salary column by using the Where clause in the update statement.

See Demo Table Online

See Demo Table Online after update

As you can see in the above graphic, all records are same except Ben’s salary column. In that case, we changed Ben’s salary column by using Ben’s emp_id column value in SQL update query, as this is unique in that table.

Update SQL with Where clause and the ‘And’ operator example

You can use multiple conditions to update table record in the Where clause. The above example used only single column (Ben’s emp_id) in the update statement. Let us use emp_id and emp_name columns to update emp_salary column value.

See Demo Table Online after update

You can see only Ben’s emp_salary column is updated in the table.

Modify multiple columns with Update statement

The example below shows changing two column values, emp_salary and emp_age of an employee Mike.

See Demo Table Online before update

See Demo Table Online after update

As you can see both columns are updated with the new values.

SQL update select: Updating data from one table to another

You can also update data from one table to another or even from one database’s table to another database table. In that case, you can use the Update with the select statement.

The update will specify a table to be updated along with column name(s) and where clause while the Select statement queries the values from the other table(s).

This is detailed in a chapter here along with the example: SQL update with select.