In This Tutorial:
- A few update statement examples
- Syntax of SQL Update statement with SET command
- Update example to alter whole column’s data
- SQL Update / set example with Where clause
- Update SQL with Where clause and the ‘And’ operator example
- Modify multiple columns data with Update statement
- The update with select: Updating data from one table to another
The Update statement in the SQL is used to update the records in the database tables. The update 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 the single update query.
A few update statement examples
Following are a few online examples that we will use in this tutorial:
Syntax of SQL Update statement with SET command
The general syntax of the Update is:
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 the 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 the same example, updating your address in your account, this is how the update command will look:
12345 Update tbl_email_accoutnsSet address = ‘new address’Where email_address=’firstname.lastname@example.org’
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:
Create table tbl_employee
PRIMARY KEY (emp_id)
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.
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.
set emp_salary = 4000
The table data will look like this after executing this update query:
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.
SQL 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.
Set emp_salary = 4000
As you can see in the above graphic, all records are the 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.
Set emp_salary = 4000
Where emp_id=5 And emp_name = 'Ben'
You can see, only Ben’s emp_salary column is updated in the table.
Modify multiple columns data with Update statement
The example below shows changing two column values; emp_salary, and emp_age of an employee Mike.
Set emp_age=36,emp_salary = 5500
Where emp_id=1 And emp_name = 'Mike'
As you can see both columns are updated with the new values.
The update with 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.