What is an UPDATE statement in SQL?
- 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 UPDATE statement is used to modify Existing Records in a Table
- The simple UPDATE statement changes all the records for the specified column. For example:
UPDATE table_name Set Col_name1 = new_value, Col_name2 = new_value ….;
This UPDATE command will update the whole data in the specified column by the new value.
- For limiting the changes only to the specific records, we use the UPDATE statement with the WHERE clause. For example:
UPDATE table_name Set Col_name1 = new_value, Col_name2 = new_value …. WHERE condition;
UPDATE syntax explained
The above two UPDATE statements also explain the syntax of using it and are 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.
An example of using an UPDATE table query
For the examples of the SQL UPDATE 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 the 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:
UPDATE sto_employees SET emp_salary = 5500 WHERE id = 2;
The data before and after 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 a single query.
For that, just separate the column names by comma and provide a new value for each column as shown in the example below:
UPDATE sto_employees SET emp_age= 31, emp_salary = 5000 WHERE id = 2;
The result-sets:
Updating a date column example
Until now we updated the numeric fields by using the UPDATE statement. In this query, the date field is updated.
By using the UPDATE command, I will update the join_date on an employee ‘Jay’ in our example table.
The query:
UPDATE sto_employees SET join_date= '2018-03-25' WHERE emp_name = 'Jay';
The record-sets before and after changing the date column:
What if we do not use the 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 is generally not required but in certain scenarios may be.
The following query updates the salary of all employees to 4000:
UPDATE sto_employees SET emp_salary = 4000;
The record-sets:
Using the SELECT statement in the UPDATE query
As such, the SQL SELECT statement is used to retrieve the records from the tables. You may use SELECT in the UPDATE query to provide the new values for the columns, as well as, in the WHERE clause of the UPDATE statement.
First, have a look at the usage of providing new values in the UPDATE query:
SELECT * FROM sto_employees WHERE emp_name = 'Ben'; SELECT * FROM sto_emp_salary_paid; UPDATE sto_emp_salary_paid SET emp_sal_paid = (SELECT emp_salary FROM sto_employees WHERE emp_name = 'Ben') WHERE emp_name = 'Ben'; SELECT * FROM sto_emp_salary_paid;
The results of all queries with demo data:
- 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 changes the record of Ben’s salary in the sto_emp_salary_paid table by the value taken from the 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:
SELECT * FROM sto_employees WHERE id = 3; SELECT * FROM sto_emp_salary_paid; UPDATE sto_emp_salary_paid SET emp_sal_paid = 4200 WHERE emp_name = (SELECT emp_name FROM sto_employees WHERE id = 3); SELECT * FROM sto_emp_salary_paid;
The results:
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 the UPDATE statement.
The example involves another table, sto_orders to show a demo query:
UPDATE sto_emp_salary_paid SET emp_sal_paid = 4200 WHERE emp_id = (SELECT DISTINCT(id) FROM sto_employees JOIN sto_orders ON sto_employees.id=sto_orders.emp_id WHERE emp_name='Mike');