SQL Update Statement with Example

5 years ago
Zaid Bin Khalid
2,421 Views
3

In this session, we will learn how to update the records in a database table using SQL.

Updating Table Data

In the previous session we have learned about insert data moreover how to select data from the SQL database table depend on specific terms. In this session, we will learn and perform one more important task which is updating the existing records in a database table.

Syntax

The UPDATE STATEMENT is used to Update given or already save data in a table.

UPDATE table_name
SET column1_name = value1, column2_name = value2,...
WHERE condition;

Here, column1_name, column2_name,… are the names of the columns or fields of a database table whose values you want to update.

Now check out some examples that shows how it works.

Let’s suppose have a table named employees in a database that has the following record.

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       1 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       5 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       3 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       4 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

Updating a Single Column

This SQL statement will update the emp_name field of the employees table and set a new value where the employee id called emp_id which is equal to 3.

UPDATE employees SET emp_name = 'updated name'
WHERE emp_id = 3;

After executing the above Statement, It shows the following output like this.

+--------+------------------+------------+--------+---------+
| emp_id | emp_name         | hire_date  | salary | dept_id |
+--------+------------------+------------+--------+---------+
|      1 | Ethan Hunt       | 2001-05-01 |   5000 |       1 |
|      2 | Tony Montana     | 2002-07-15 |   6500 |       5 |
|      3 | updated name     | 2005-10-18 |   8000 |       3 |
|      4 | Rick Deckard     | 2007-01-03 |   7200 |       4 |
|      5 | Martin Blank     | 2008-06-24 |   5600 |    NULL |
+--------+------------------+------------+--------+---------+

Updating Multiple Columns

You can update multiple columns in your database by applying a list of comma-separated column names and value pair.

UPDATE employees
SET salary = 8000, dept_id = 2
WHERE emp_id = 5;

After executing the above Statement, It show the following output like this:

+--------+------------------+------------+--------+---------+
| emp_id | emp_name         | hire_date  | salary | dept_id |
+--------+------------------+------------+--------+---------+
|      1 | Ethan Hunt       | 2001-05-01 |   5000 |       1 |
|      2 | Tony Montana     | 2002-07-15 |   6500 |       5 |
|      3 | Sarah Ann Connor | 2005-10-18 |   8000 |       3 |
|      4 | Rick Deckard     | 2007-01-03 |   7200 |       4 |
|      5 | Martin Blank     | 2008-06-24 |   8000 |       2 |
+--------+------------------+------------+--------+---------+