SQL UPDATE Query

By | October 2, 2021
SQL UPDATE Query

To modify the existing table data we use the SQL UPDATE command and it works along with the SET command. The UPDATE command selects the table from the database and the SET command modify the data.

When we use the UPDATE query on the table we also use the WHERE clause to filter the specific location or data set which we want to modify.

Vamware

UPDATE Syntax

Follow this syntax to perform the UPDATE query:

UPDATE table_name
SET column_name_1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Example

For this example considered this students table:

+------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
|    6 | Robin  | NULL | NULL   |   860 |
+------+--------+------+--------+-------+

Query: Update the grades of Robin and set it B:

UPDATE students
SET grades ="B"
WHERE name = "Robin";

Output

Query OK, 1 row affected (0.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query: Verify the Update:

 SELECT *
 FROM students;

Output

+------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
|    6 | Robin  | NULL | B      |   860 |
+------+--------+------+--------+-------+

Need for Using WHERE Clause

While using the UPDATE command we should always use the where command if you forget to use the WHERE clause then the update operation would perform on the complete table and all the data will be modified.

For example

For this example, we will use the backup table of students., whose records are:

+------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
|    6 | Robin  | NULL | B      |   860 |
+------+--------+------+--------+-------+

Query: Reset all students marks to 0:

UPDATE student_backup
SET marks= 0;

Output

Query OK, 6 rows affected (0.40 sec)
Rows matched: 6 Changed: 6 Warnings: 0

Query: Verify the Update:

SELECT *
FROM student_backup;

Output

+------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |     0 |
|    2 | Naruto |   18 | A      |     0 |
|    3 | Zoro   |   20 | A      |     0 |
|    4 | Sanji  |   21 | B      |     0 |
|    5 | Nami   |   17 | B      |     0 |
|    6 | Robin  | NULL | B      |     0 |
+------+--------+------+--------+-------+

Summary

  • UPDATE command is used to select the table in which we want to modify the data.
  • SET command modify the data sets.
  • Always use the WHERE clause to filter the data sets.

People are also reading:

Leave a Reply

Your email address will not be published. Required fields are marked *