SQL UPDATE Statement

The UPDATE statement in SQL is used to update records in the table. We can modify one or multiple records (rows) in table using UPDATE statement.

Note: The where clause plays an important role in UPDATE statement, where clause is the one that specifies which records needs to be updated using UPDATE statement. If you do not use where clause in UPDATE statement, all the records in the table will be updated with the specified values.

UPDATE statement Syntax

UPDATE table_name
SET column_name1 = value_1, column_name2 = value_2, ...
WHERE condition;

SQL UPDATE statement Example

Table: Students

ID    Name       Age    Section    ClassRoom
---   -----      ---    --------   ---------
890   Chaitanya  23      CSE1       56
891   Steve      24      CSE1       56
892   Ron        23      CSE2       57
893   Juan       25      CSE2       57
894   Paul       26      CSE3       58

Lets say we want to merge all three sections: CSE1, CSE2 & CSE3. The following SQL statement will update the SECTION and CLASSROOM of CSE2 and CSE3 students. This statement will update multiple records.

UPDATE Students
SET Section = 'CSE1', ClassRoom = 56
WHERE Section = CSE2 OR Section = CSE3;

Result:

ID    Name       Age    Section    ClassRoom
---   -----      ---    --------   ---------
890   Chaitanya  23      CSE1       56
891   Steve      24      CSE1       56
892   Ron        23      CSE1       56
893   Juan       25      CSE1       56
894   Paul       26      CSE1       56

Take some precautions prior to executing your UPDATE statement

As I mentioned in the beginning of this guide that while executing UPDATE statement, if you do not use the where clause, all the records (rows) will be updated in the table.

For example:
Lets take the same table that we have seen in the above example:
Table: Students

ID    Name       Age    Section    ClassRoom
---   -----      ---    --------   ---------
890   Chaitanya  23      CSE1       56
891   Steve      24      CSE1       56
892   Ron        23      CSE2       57
893   Juan       25      CSE2       57
894   Paul       26      CSE3       58

Lets say we want to rename the student name from “Chaitanya” to “Chetan”. The SQL update statement for this should be like this:
The following SQL statement will update the name of the student from “Chaitanya” to “Chetan”.

UPDATE Students
SET Name = 'Chetan'
WHERE Name = 'Chaitanya';

What happens when we don’t use where clause?
If we hadn’t use the where clause in above SQL statement, the Query should be like this:

Wrong Way of updating table:

UPDATE Students
SET Name='Chetan';

Result:

ID    Name       Age    Section    ClassRoom
---   -----      ---    --------   ---------
890   Chetan     23      CSE1       56
891   Chetan     24      CSE1       56
892   Chetan     23      CSE2       57
893   Chetan     25      CSE2       57
894   Chetan     26      CSE3       58

As you can see, when we do not use the where clause, all the records in the table are updated, which is not what we wanted to do, so take precautions while executing UPDATE statement.