UPDATE Operation
The UPDATE statement is used to modify existing records in a SQL Server table.
When information already stored in a table needs to be changed, we use the UPDATE statement. For example, we can update a student's city, age, fees or any other existing information.
What is UPDATE?
The UPDATE statement is used to change existing values in one or more rows of a table.
The SET clause specifies the new value, while the WHERE clause identifies which records should be modified.
UPDATE Syntax
UPDATE table_name
SET column_name = new_value
WHERE condition;
Example Table
We will use the following Students table for the examples.
| StudentId | StudentName | Age | City |
|---|---|---|---|
| 101 | Rahul | 22 | Pune |
| 102 | Priya | 23 | Mumbai |
| 103 | Amit | 21 | Nashik |
| 104 | Neha | 22 | Pune |
Update a Single Record
Suppose we want to change Rahul's city from Pune to Mumbai.
UPDATE Students
SET City = 'Mumbai'
WHERE StudentId = 101;
Output
After executing the query, Rahul's City value is changed from Pune to Mumbai.
| StudentId | StudentName | Age | City |
|---|---|---|---|
| 101 | Rahul | 22 | Mumbai |
| 102 | Priya | 23 | Mumbai |
| 103 | Amit | 21 | Nashik |
| 104 | Neha | 22 | Pune |
Update Multiple Columns
Multiple columns can be updated in the same UPDATE statement.
UPDATE Students
SET Age = 24,
City = 'Pune'
WHERE StudentId = 102;
This query changes both the Age and City of the student whose StudentId is 102.
UPDATE Using a Condition
We can use conditions to update records that satisfy a particular requirement.
UPDATE Students
SET City = 'Pune'
WHERE Age = 22;
This statement updates the City for all students whose Age is 22.
UPDATE Without WHERE
If the WHERE clause is omitted, the UPDATE statement affects all rows in the table.
UPDATE Students
SET City = 'Pune';
Updating a String Value
String values are written inside single quotation marks.
UPDATE Students
SET StudentName = 'Rahul Sharma'
WHERE StudentId = 101;
Updating a Numeric Value
Numeric values do not require quotation marks.
UPDATE Students
SET Age = 25
WHERE StudentId = 101;
Practical Example
Suppose a student changes their city after admission. We can update the student's existing record using StudentId.
UPDATE Students
SET City = 'Bangalore'
WHERE StudentId = 103;
Only the record with StudentId 103 will be modified.
Important Points
- UPDATE is used to modify existing records.
- SET is used to specify the new value.
- WHERE is used to identify the records to update.
- Multiple columns can be updated in one statement.
- Without WHERE, all rows may be updated.
- String values should be enclosed in single quotes.
Summary
In this section, we learned how the UPDATE statement modifies existing records in a SQL Server table. We covered updating a single column, multiple columns, using conditions, and the importance of using the WHERE clause.