SQL UPDATE Query | Modify Existing Records in Table

The UPDATE Query in SQL updates existing records in the table. You can update a single as well as multiple columns using the query. Also, we can specify which rows to update by using the WHERE Clause. In this article, we will discuss SQL UPDATE Query. Also, we will discuss a few examples of writing it.

Syntax

UPDATE table_name SET column1 = value1, column2 = value2 … WHERE condition;

• table_name: Specify the table name in which to update.
• column1, column2: The names of the columns which you need to update.
• value1, value2: The new values of the columns.
• condition: The WHERE Condition to determine which rows to update.

Note: If you don’t mention the WHERE condition, then the UPDATE Query will update all the rows of the table in SQL. Therefore, you should always mention the condition if you need to update some particular rows.

Demo Table

For instance, consider the demo table to refer to all the queries in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Examples

Let’s consider a few examples of writing UPDATE Queries in SQL.

Example 1: Simple UPDATE on One Column

For instance, consider an example of updating one column in the above demo table.

UPDATE employees SET salary = 21000 WHERE name = 'David';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The above UPDATE query in SQL updates the salary to 21000 of the row with the name David. The updated row will now look like the following:

mysql> SELECT * FROM employees WHERE name = 'David';
+----+-------+--------+------------+-------+
| id | name  | salary | experience | team  |
+----+-------+--------+------------+-------+
|  1 | David |  20000 |          5 | Alpha |
+----+-------+--------+------------+-------+

Example 2: Updating Multiple Rows

The SQL UPDATE Query will also update multiple rows if the WHERE condition satisfies it. For example, consider the example below:

mysql> UPDATE employees SET experience = experience + 1 WHERE experience > 5;
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

The query updates all the rows which have experience column greater than 5. In our table, it updates 2 rows and the final table becomes:

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          7 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          9 | Alpha |
+----+--------+--------+------------+-------+

Example 3: Updating Multiple Columns

Similarly, we can also update multiple columns in the table by specifying them in the UPDATE query in SQL.

mysql> UPDATE employees SET salary = 26000, team = 'Alpha' WHERE name = 'Monica';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In the above query, we update the salary and team column of the row with the name Monica.

mysql> SELECT * FROM employees WHERE name = 'Monica';
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  2 | Monica |  26000 |          7 | Alpha |
+----+--------+--------+------------+-------+

Example 4: Omitting WHERE Condition

However, if you omit the WHERE Condition, the query will perform the update on all the rows.

mysql> UPDATE employees SET name = 'Mike';
Query OK, 1 row affected (0.06 sec)
Rows matched: 5  Changed: 5  Warnings: 0

+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | Mike   |  20000 |          5 | Alpha |
|  2 | Mike   |  25000 |          7 | Beta  |
|  3 | Mike   |  15000 |          3 | Beta  |
|  4 | Mike   |  18000 |          4 | Gamma |
|  5 | Mike   |  30000 |          9 | Alpha |
+----+--------+--------+------------+-------+

Conclusion

In conclusion, we discussed the SQL UPDATE Query in this article. You can read more about it on Wikipedia. Also, you can read more about SQL Server on Concatly.