SQL Topics
UPDATE Data
title: UPDATE Data
Databases are constantly changing. Employees receive salary increases, customers update their contact information, products change prices, and students modify their personal details. To keep information accurate and up to date, databases must support data modification.
SQL provides the UPDATE statement for this purpose.
The UPDATE statement allows developers to modify existing records in a table without deleting and re-entering the data. It is one of the most important Data Manipulation Language (DML) commands and is used extensively in real-world applications.
Whenever a user edits their profile, changes a password, updates an address, or modifies account information, an UPDATE statement is typically executed behind the scenes.
In this lesson, you will learn how UPDATE works, how to modify single and multiple columns, use conditions safely, avoid common mistakes, and follow professional best practices.
What is UPDATE?
The UPDATE statement is used to modify existing records in a database table.
Example:
UPDATE Students
SET Age = 21
WHERE StudentID = 1;Before:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 20 |
After:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 21 |
The existing record is updated without creating a new row.
Why is UPDATE Important?
Information changes over time.
Examples include:
- Customer address changes
- Employee salary updates
- Product price modifications
- Student information corrections
- Inventory adjustments
Without UPDATE:
Incorrect Data
↓
Poor DecisionsWith UPDATE:
Accurate Data
↓
Reliable InformationMaintaining accurate data is essential for every database application.
Basic UPDATE Syntax
The general syntax is:
UPDATE TableName
SET ColumnName = Value
WHERE Condition;Example:
UPDATE Students
SET Age = 21
WHERE StudentID = 1;This updates the Age column for the specified student.
Understanding the Syntax
Example:
UPDATE Students
SET Age = 21
WHERE StudentID = 1;UPDATE
Specifies the table to modify.
Students
Table name.
SET
Defines which column should be updated.
Age = 21
New value.
WHERE
Identifies the target record.
Without WHERE, every row may be modified.
Creating a Sample Table
Example:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100),
Age INT
);Insert data:
INSERT INTO Students
VALUES
(1, 'Rahul', 20),
(2, 'Priya', 21),
(3, 'Amit', 19);Current data:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 20 |
| 2 | Priya | 21 |
| 3 | Amit | 19 |
Updating a Single Record
Example:
UPDATE Students
SET Age = 22
WHERE StudentID = 2;Result:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 20 |
| 2 | Priya | 22 |
| 3 | Amit | 19 |
Only one row is modified.
Updating Multiple Columns
More than one column can be updated at the same time.
Example:
UPDATE Students
SET Name = 'Rahul Kumar',
Age = 21
WHERE StudentID = 1;Result:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul Kumar | 21 |
Both columns are updated in a single query.
Updating Multiple Records
A condition may match multiple rows.
Example:
UPDATE Students
SET Age = 18
WHERE Age < 18;All matching rows are updated.
This is useful when applying bulk modifications.
Updating All Records
If WHERE is omitted:
UPDATE Students
SET Age = 25;Result:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 25 |
| 2 | Priya | 25 |
| 3 | Amit | 25 |
Every row is modified.
This is one of the most common beginner mistakes.
Using Mathematical Operations
UPDATE can perform calculations.
Example:
UPDATE Employees
SET Salary = Salary + 5000;Current data:
| Employee | Salary |
|---|---|
| Rahul | 40000 |
Result:
| Employee | Salary |
|---|---|
| Rahul | 45000 |
Useful for salary increases and inventory adjustments.
Updating Text Values
Example:
UPDATE Customers
SET City = 'Mumbai'
WHERE CustomerID = 10;The city value is updated.
Updating Date Values
Example:
UPDATE Orders
SET OrderDate = CURRENT_DATE
WHERE OrderID = 100;The current date is stored.
Useful for tracking modifications.
Updating NULL Values
Suppose:
| StudentID | |
|---|---|
| 1 | NULL |
Update:
UPDATE Students
SET Email = 'rahul@example.com'
WHERE StudentID = 1;Result:
| StudentID | |
|---|---|
| 1 | rahul@example.com |
NULL is replaced with actual data.
Real-World Example
Imagine an e-commerce application.
Products table:
| ProductID | ProductName | Price |
|---|---|---|
| 101 | Laptop | 60000 |
Price reduction:
UPDATE Products
SET Price = 55000
WHERE ProductID = 101;Result:
| ProductID | ProductName | Price |
|---|---|---|
| 101 | Laptop | 55000 |
Customers now see the updated price.
UPDATE with Conditions
Conditions allow precise modifications.
Example:
UPDATE Employees
SET Salary = Salary + 3000
WHERE Department = 'IT';Only IT employees receive the increase.
This demonstrates the importance of WHERE clauses.
Common Errors
Forgetting WHERE Clause
Dangerous:
UPDATE Students
SET Age = 30;Every row is updated.
Always verify the condition.
Updating Wrong Records
Example:
UPDATE Students
SET Age = 21
WHERE Name = 'Rahul';Multiple Rahuls may exist.
Prefer unique identifiers such as StudentID.
Invalid Data Types
Wrong:
UPDATE Students
SET Age = 'Twenty';If Age expects INT:
Invalid Data Type ErrorConstraint Violations
Example:
UPDATE Users
SET Email = NULL;If Email is NOT NULL:
Constraint ViolationBest Practices
Always Use WHERE
Before executing UPDATE:
SELECT *
FROM Students
WHERE StudentID = 1;Verify the affected rows.
Use Primary Keys
Prefer:
WHERE StudentID = 1instead of names.
Backup Important Data
For critical systems, create backups before large updates.
Test Queries First
Run a SELECT query with the same condition.
Example:
SELECT *
FROM Students
WHERE StudentID = 1;Avoid Unnecessary Updates
Only modify records when required.
Common Interview Questions
What is UPDATE used for?
UPDATE modifies existing records in a table.
What happens if WHERE is omitted?
All rows in the table are updated.
Can multiple columns be updated?
Yes.
Example:
UPDATE Students
SET Name = 'Rahul Kumar',
Age = 21
WHERE StudentID = 1;Why is WHERE important?
It controls which records are modified.
Without it, every row may be affected.
Summary
The UPDATE statement is one of the most important SQL commands because it allows existing information to be modified efficiently without deleting records.
In this lesson, you learned:
- What UPDATE is
- Why it is important
- Basic syntax
- Updating single records
- Updating multiple columns
- Updating multiple rows
- Mathematical updates
- Handling NULL values
- Best practices
- Common mistakes
Understanding UPDATE is essential because maintaining accurate and current information is a fundamental requirement of every database system.
Next Step
Continue to the next lesson:
DELETE Data →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for UPDATE Data.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, crud, update
Related SQL Topics