What is UPDATE Statement and Why is it Used?
If you want to change existing data in a table, use the UPDATE statement. It changes the value of one or more columns in existing rows.
A table contains many rows, and you can use the UPDATE statement to modify a row. You can update one or more columns in one or more rows, and if you want to update specific rows, you can use the WHERE clause.
Syntax:
UPDATE table_name SET column_name = new_value WHERE condition;
Parameter:
- table_name: Specifies the name of the table where you want to change the data.
- column_name: This column has the value that you want to change.
- new_value: Specifies the new value that you want to store in the column.
- condition: Specifies which rows should be changed. The WHERE clause is used to set this condition.
Example:
Suppose the StudentA table contains the rows of three students with their ages. Now, you want to change Rahul's age from 20 to 22.
StudentA Table:
┌────────────┬─────────┬─────┐ │ Student_ID │ Name │ Age │ ├────────────┼─────────┼─────┤ │ 101 │ Rahul │ 20 │ │ 102 │ Amit │ 21 │ │ 103 │ Neha │ 19 │ └────────────┴─────────┴─────┘
Now, you will execute the UPDATE statement:
UPDATE StudentA SET Age = 22 WHERE Student_ID = 101;
Then, Rahul's age changes from 20 to 22. The other student rows remain unchanged.
Output:
StudentA Table:
┌────────────┬─────────┬─────┐ │ Student_ID │ Name │ Age │ ├────────────┼─────────┼─────┤ │ 101 │ Rahul │ 22 │ │ 102 │ Amit │ 21 │ │ 103 │ Neha │ 19 │ └────────────┴─────────┴─────┘
How to Update Multiple Columns Using the UPDATE Statement?
If you want to change more than one column in the same row, you can use the UPDATE statement with multiple column values in the SET clause.
For example,Let, you want to change Rahul's name to Rohan and age to 23.
UPDATE StudentA SET Name = 'Rohan', Age = 23 WHERE Student_ID = 101;
After running this statement, both the Name and Age values of the student with Student_ID 101 are updated.
How to Update Multiple Rows Using the UPDATE Statement?
If you want to change the same value for multiple rows, you can use a condition that matches more than one row.
For example, let’s say you want to increase the age of all students by 1, so each student’s age will increase by 1.
UPDATE StudentA SET Age = Age + 1;
After running this statement, the age of every student in the StudentA table increases by 1.
Common Mistakes When Using the UPDATE Statement
If you use the UPDATE statement on a table,you should carefully avoid the following mistakes:
- Forgetting the WHERE Clause: You should use the WHERE clause when you want to change only specific rows. The UPDATE statement can change the value in all rows of the table if you do not use the WHERE clause.
- Using the Wrong WHERE Condition: You should carefully check the WHERE condition before running the statement. A wrong condition can modify rows that you did not want to change.
- Choosing the Wrong Column: You should check the column name before updating data. If you select the wrong column, an incorrect value may be changed.
- Entering the Wrong Value: Before executing the UPDATE statement, you should check the new value. An incorrect value can change the existing data incorrectly.
SQL Update with Multiple Conditions
If you want to update data only when two or more conditions are true, you can use multiple conditions with the WHERE clause. When all conditions will be true, the AND operator is applied.
For example, suppose you want to change Rahul's age to 22 only when his Student_ID is 101 and his current age is 20.
UPDATE StudentA SET Age = 22 WHERE Student_ID = 101 AND Age = 20;
Output:
Both conditions are true, so Rahul's age changes to 22.
SQL UPDATE with Comparison Operators
If you want to update data by checking a value, you can use comparison operators with the WHERE clause. Common comparison operators are =, >, =, .
For example, suppose you want to increase the age by 1 for students whose age is less than 21.
UPDATE StudentA SET Age = Age + 1 WHERE Age < 21;
Output:
The age of all students whose age is less than 21 is increased by 1.
SQL UPDATE with LIKE
If you want to update data for rows that have a specific text pattern, you can use the LIKE operator with the WHERE clause.
For example, suppose you want to change the age to 22 for students whose names start with the letter R.
UPDATE StudentA SET Age = 22 WHERE Name LIKE 'R%';
Output:
The age of every student whose name starts with R is changed to 22.
SQL UPDATE with IN
If you want to update data for rows that match any one of several given values, you can use the IN operator with the WHERE clause.
For example, if you want to set the age to 22 for students whose Student_ID is 101 or 103.
UPDATE StudentA SET Age = 22 WHERE Student_ID IN (101, 103);
Output:
The age of the students with Student_ID 101 and 103 is updated to 22.
SQL UPDATE with NULL
Which column has no value and whose rows you want to update, then you should use the WHERE clause with IS NULL.
For example, suppose some students do not have an age value. You want to set their age to 18.
UPDATE StudentA SET Age = 18 WHERE Age IS NULL;
Output:
The age of all students whose Age value is NULL is changed to 18.
IS NOT NULL can be used when you want to update rows where a column already has a value.
UPDATE StudentA SET Age = 20 WHERE Age IS NOT NULL;
SQL UPDATE Using Another Table
UPDATE with JOIN can be used when you want to update data in one table using data from another table.
For example, suppose you have a StudentA table and a CourseA table. You want to update the course name in the StudentA table using the matching course name from the CourseA table.
UPDATE StudentA s JOIN CourseA c ON s.Course_ID = c.Course_ID SET s.Course_Name = c.Course_Name;
Output:
The Course_Name in the StudentA table is updated using the matching value from the CourseA table.
In this example, the JOIN connects both tables using the matching Course_ID. The SET clause changes the course name in the StudentA table.
SQL UPDATE Using a Subquery
The value from the AgeData table can be used to change the age of Student_ID 101.
For example, suppose there are two tables: StudentA and AgeData. You can use the age from the AgeData table to update Student_ID 101 in the StudentA table.
UPDATE StudentA SET Age = ( SELECT Age FROM AgeData WHERE Student_ID = 101 ) WHERE Student_ID = 101;
Output:
The age of the student with Student_ID 101 is updated using the age value returned by the subquery from the AgeData table.
The age value for Student_ID 101 can be changed by taking the required age from the Age Data table.
The UPDATE statement then uses this value to update the age in the StudentA table.
