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

    Previous Tutorial Next Tutorial