SQL UPDATE Command

An infographic guide to updating records in relational databases, featuring syntax insights, conditional updates, and best practices for efficient data management.

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.
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 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:

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 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:

SQL UPDATE multiple

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:

SQL UPDATE date

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:

SQL UPDATE whole

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:

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 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:

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 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');

 

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!