SQL UPDATE Statement

The SQL UPDATE statement is used when information already stored in a database needs to be changed. Instead of creating another row, UPDATE modifies selected values in existing records. It is therefore an important SQL command for maintaining information as real-world situations change.

For example, a student's marks may need correction, an employee may move to another department, a product price may change, or a customer's contact information may be edited. All of these situations can be handled with an UPDATE query.

The most important part of an UPDATE operation is identifying the correct rows. A carefully written WHERE condition helps ensure that only the intended records are changed. Before executing a large update, it is also good practice to review the condition and understand how many rows it can affect.

What Does UPDATE Do in SQL?

UPDATE belongs to SQL's Data Manipulation Language (DML). It changes values stored in one or more columns of existing rows while leaving the table structure unchanged.

Depending on the condition supplied with the query, an UPDATE operation may affect one row, several rows, or every row in a table. The database does not automatically know which record you intended, so the condition should be written carefully.

Important Parts of an UPDATE Query

An UPDATE statement generally contains three important components:

UPDATE: Specifies the table whose data will be changed.
SET: Defines the column values that should be modified.
WHERE: Identifies the rows that should receive the change.

The WHERE clause is optional from a syntax perspective, but omitting it means that the SET operation can apply to every row in the table.

Basic Syntax of UPDATE

UPDATE table_name
SET column_name = new_value
WHERE condition;

For example, a particular student's marks can be changed using a unique StudentID:

UPDATE Student
SET Marks = 87
WHERE StudentID = 101;

Only the row matching StudentID 101 is targeted by this query.

Sample Student Table

StudentID	StudentName	Course	Marks
101	Rahul	BCA	80
102	Priya	BCA	85
103	Amit	MCA	78
104	Neha	B.Tech	90

Updating One Row

A common database task is correcting or changing one particular record. A primary key or another unique identifier is usually a good choice for locating that record.

Example

UPDATE Student
SET Marks = 88
WHERE StudentID = 101;

After execution, the marks stored for student 101 become 88. The other students remain unchanged.

Result

StudentID	StudentName	Marks
101	Rahul	88

Updating More Than One Row

An UPDATE statement can intentionally affect several rows. This is useful when the same change applies to every record satisfying a particular condition.

Example

UPDATE Student
SET Course = 'BCA Advanced'
WHERE Course = 'BCA';

Every student currently associated with the BCA course is changed to BCA Advanced.

This demonstrates why the WHERE condition should be checked before execution. A condition matching many rows produces a bulk update rather than a single-record update.

Updating Several Columns at Once

There is no requirement to create a separate UPDATE statement for every column. Multiple column assignments can be included in the same SET clause.

UPDATE Student
SET Course = 'MCA',
    Marks = 91
WHERE StudentID = 102;

The query changes both the course and marks of the selected student.

Using WHERE with UPDATE

WHERE determines which existing rows qualify for modification. It can use comparison operators, logical operators, ranges, lists, and other SQL expressions.

UPDATE Employee
SET Salary = 55000
WHERE EmployeeID = 12;

Only the employee whose EmployeeID is 12 is selected.

Why WHERE Matters

Limits the number of affected records.
Helps prevent unintended changes.
Makes the purpose of the query clearer.
Allows precise record selection.
Is especially important during bulk operations.

UPDATE Without a WHERE Clause

If WHERE is not included, the SET expression applies to every row in the target table.

UPDATE Student
SET Status = 'Active';

This statement assigns Active to the Status column for every row in Student. Such a query can be completely valid when the requirement really is to change all records, but it should never be executed accidentally.

Updating Text Data

Character columns such as names, departments, cities, and categories can be changed by placing the new text value inside quotes.

UPDATE Employee
SET Department = 'Human Resources'
WHERE Department = 'HR';

Every employee whose department value is HR is assigned the new department name.

Updating Numeric Data

Numbers can be assigned directly or calculated from the existing value.

UPDATE Product
SET Price = 1299
WHERE ProductID = 25;

The price of product 25 is changed to 1299.

Updating a Value Using an Expression

The SET clause can calculate a new value from the existing value. This is particularly useful for salary revisions, price changes, stock adjustments, and similar operations.

Increasing Salary by 10 Percent

UPDATE Employee
SET Salary = Salary * 1.10
WHERE Department = 'IT';

The existing salary of each matching IT employee is multiplied by 1.10.

Adding Marks

UPDATE Student
SET Marks = Marks + 5
WHERE Course = 'BCA';

Five marks are added to students belonging to the selected course.

UPDATE with AND Condition

Multiple conditions can be combined when a change should apply only to a narrower group of records.

UPDATE Employee
SET Bonus = 10000
WHERE Department = 'IT'
AND Experience > 5;

The bonus is assigned only to IT employees whose experience is greater than five years.

UPDATE with OR Condition

OR can be used when more than one condition should qualify a row.

UPDATE Student
SET Status = 'Eligible'
WHERE Marks >= 75
OR Attendance >= 80;

A student is marked Eligible when either condition is satisfied.

UPDATE with IN Operator

IN is useful when several known values should be matched without writing a long series of OR conditions.

UPDATE Student
SET Status = 'Selected'
WHERE StudentID IN (101, 103, 104);

Only the three listed students are selected for the update.

UPDATE with BETWEEN

BETWEEN can be used when a condition applies to values inside a specified range.

UPDATE Employee
SET Bonus = 5000
WHERE Salary BETWEEN 30000 AND 50000;

Employees whose salary falls within the specified range receive the assigned bonus.

UPDATE with NULL Values

SQL uses NULL to represent the absence of a value. NULL should be tested with IS NULL rather than the normal equality operator.

UPDATE Employee
SET Department = 'Unassigned'
WHERE Department IS NULL;

Only rows where Department currently contains NULL are modified.

Setting a Value to NULL

An existing value can also be cleared by assigning NULL, provided the column allows NULL values.

UPDATE Customer
SET AlternatePhone = NULL
WHERE CustomerID = 105;

Updating Date and Time Values

Date or timestamp columns can also be modified. The exact literal format and functions may differ between database systems, so the syntax should be checked for the SQL platform being used.

UPDATE Employee
SET JoiningDate = '2026-07-15'
WHERE EmployeeID = 8;

The joining date for the selected employee is changed to the specified date.

UPDATE with CASE Expression

CASE is useful when different rows need different new values based on conditions. Instead of executing several UPDATE statements, a single statement can contain conditional logic.

UPDATE Student
SET Grade =
CASE
    WHEN Marks >= 90 THEN 'A'
    WHEN Marks >= 75 THEN 'B'
    WHEN Marks >= 60 THEN 'C'
    ELSE 'D'
END;

The database evaluates the student's marks and assigns a corresponding grade.

Why Use CASE?

Allows several conditions in one update.
Reduces repetitive SQL statements.
Keeps related business rules together.
Can make bulk modifications easier to maintain.

UPDATE Using a Subquery

A subquery can be used when the new value or selection condition depends on information calculated from another query.

UPDATE Employee
SET Salary =
(
    SELECT AVG(Salary)
    FROM Employee
)
WHERE Department = 'Training';

The example assigns the calculated overall average salary to employees in the Training department. Actual production queries should be designed carefully when the source and target data come from the same table.

UPDATE Based on Another Table

In applications containing related tables, an update may depend on information stored somewhere else. The exact syntax for this operation varies between database systems.

Example for MySQL-Style UPDATE JOIN

UPDATE Employee AS E
INNER JOIN Department AS D
ON E.DepartmentID = D.DepartmentID
SET E.Location = D.Location;

This example copies the department location into the corresponding employee records.

UPDATE JOIN syntax is not identical across all SQL products. For portable applications, developers should use the syntax supported by their particular database system.

UPDATE with EXISTS

EXISTS can be useful when rows should be changed only if a related record is present.

UPDATE Customer AS C
SET C.Status = 'Active'
WHERE EXISTS
(
    SELECT 1
    FROM Orders AS O
    WHERE O.CustomerID = C.CustomerID
);

The idea behind this query is to activate customers for whom at least one related order exists.

Updating Empty String Values

An empty string and NULL are not necessarily the same thing. If a column contains an empty string, it can be searched using an equality condition.

UPDATE Customer
SET Email = 'Not Provided'
WHERE Email = '';

This changes empty email strings to the specified placeholder text.

Practical Example: Employee Management

Suppose an employee receives a promotion and needs a new job designation.

UPDATE Employee
SET Designation = 'Senior Developer'
WHERE EmployeeID = 20;

The query changes the designation for the selected employee without creating a new employee record.

Practical Example: College Database

A college system may need to correct marks after verification.

UPDATE Student
SET Marks = 92
WHERE StudentID = 205;

The marks stored for student 205 are replaced with the corrected value.

Practical Example: E-Commerce Database

An online store may update inventory after receiving a shipment.

UPDATE Product
SET Stock = Stock + 100
WHERE ProductID = 40;

The existing stock is increased by 100 units.

Practical Example: Banking Database

A banking application may need to update an account's status after a defined period of inactivity.

UPDATE Account
SET Status = 'Inactive'
WHERE LastTransactionDate < '2025-01-01';

The condition selects accounts whose recorded last transaction date is earlier than the specified date.

Practical Example: Hospital Database

A hospital application may update a patient's assigned doctor after a scheduling change.

UPDATE Patient
SET DoctorID = 18
WHERE PatientID = 302;

The doctor reference for the selected patient is changed.

Using Transactions with UPDATE

When several related modifications must succeed together, transactions can provide an important layer of control. A transaction allows changes to be committed as a unit or rolled back when the operation needs to be cancelled.

START TRANSACTION;

UPDATE Account
SET Balance = Balance - 1000
WHERE AccountID = 101;

UPDATE Account
SET Balance = Balance + 1000
WHERE AccountID = 202;

COMMIT;

This example represents the two sides of a simple transfer. Transaction behavior and syntax can vary between database engines and storage configurations.

Using ROLLBACK

ROLLBACK is used to undo changes that belong to an active transaction and have not yet been committed.

START TRANSACTION;

UPDATE Account
SET Balance = Balance - 500
WHERE AccountID = 101;

ROLLBACK;

The uncommitted update is cancelled when the transaction is rolled back.

UPDATE and COMMIT

COMMIT confirms the changes made within a transaction.

START TRANSACTION;

UPDATE Student
SET Marks = 95
WHERE StudentID = 101;

COMMIT;

After the transaction is successfully committed, the change becomes part of the database state according to the database system's transaction rules.

Checking Rows Before Performing an UPDATE

A useful safety practice is to first run a SELECT query using the same WHERE condition that will later be used by UPDATE.

Step 1: Check the Records

SELECT *
FROM Employee
WHERE Department = 'IT'
AND Experience > 5;

Step 2: Perform the Update

UPDATE Employee
SET Bonus = 10000
WHERE Department = 'IT'
AND Experience > 5;

This approach makes it easier to verify which rows are going to be affected before changing their values.

Checking the Number of Affected Rows

Database tools and application APIs commonly provide the number of rows affected by an UPDATE operation. Reviewing this information can help detect unexpected results, especially during bulk changes.

For example, if an operation was expected to modify five rows but the database reports several thousand affected rows, the query should be investigated before proceeding with further work.

Performance Considerations for UPDATE

Updating a large number of rows can require substantial database resources. The engine may need to locate qualifying records, modify data pages, maintain indexes, and enforce constraints or triggers.

For this reason, large update operations should be planned rather than executed blindly.

Useful Practices

Use an appropriate WHERE condition.
Update only the columns that actually need changes.
Review indexes supporting frequently used search conditions.
Test important queries before running them against production data.
Consider transaction size for very large operations.
Monitor locks and system resources when performing bulk updates.
Keep backups and recovery procedures available for important data.

Can Indexes Improve UPDATE Performance?

Indexes can help the database locate rows that satisfy a WHERE condition. However, indexes also have a maintenance cost because changing indexed column values may require corresponding index updates.

Therefore, indexes should be designed according to the overall workload rather than being added automatically to every column used in UPDATE statements.

UPDATE vs INSERT

UPDATE	INSERT
Changes existing rows.	Adds new rows.
Modifies stored column values.	Creates a new record.
Can target rows with WHERE.	Usually supplies values for a new row.
Does not normally increase the row count.	Normally increases the row count.

UPDATE vs DELETE

UPDATE	DELETE
Changes values in existing rows.	Removes selected rows.
The row continues to exist.	The selected row is removed from the table.
Used for modification or correction.	Used when records are no longer required.

UPDATE vs ALTER TABLE

UPDATE	ALTER TABLE
Changes data stored in rows.	Changes the structure of a table.
Used for DML operations.	Used for DDL operations.
Can modify column values.	Can add, remove, or modify columns depending on the database system.

Common UPDATE Mistakes

1. Forgetting the WHERE Condition

UPDATE Student
SET Marks = 100;

This statement targets every row. It should only be used when changing all records is genuinely intended.

2. Using the Wrong Condition

A syntactically correct UPDATE can still produce incorrect results if its WHERE condition identifies the wrong records. Always inspect the condition before execution.

3. Using Incorrect Data Types

The value assigned to a column should be compatible with its definition. Database systems may reject incompatible values or convert them according to their rules.

4. Ignoring Constraints

Primary keys, foreign keys, CHECK constraints, NOT NULL restrictions, triggers, and other database rules may affect whether an UPDATE succeeds.

5. Updating More Rows Than Expected

Bulk updates should be tested carefully. A SELECT statement using the same condition is a simple way to inspect the intended target set.

Advantages of UPDATE Statement

Changes existing information without recreating rows.
Can modify one or many records.
Supports conditional modifications.
Can update several columns in one statement.
Can work with expressions and conditional CASE logic.
Can participate in transactions.
Useful in almost every database-driven application.

Limitations and Risks of UPDATE

An incorrect condition can change unintended records.
A missing WHERE clause may affect the entire table.
Large updates can consume significant database resources.
Updates may be restricted by constraints or triggers.
Complex update queries can be difficult to troubleshoot.
Recovery planning is important for high-impact operations.

Best Practices for SQL UPDATE

Understand the target table before modifying data.
Use a precise WHERE condition.
Run a SELECT with the same condition before important updates.
Use transactions when multiple changes must be treated as one operation.
Review the number of affected rows.
Test large updates in a safe environment first.
Keep appropriate backup and recovery procedures.
Do not use DISTINCT or other unrelated clauses as a substitute for correct update logic.

SQL UPDATE Statement Interview Questions and Answers

1. What is UPDATE in SQL?

UPDATE is a DML statement used to modify values in existing database rows.

2. What is the basic syntax of UPDATE?

UPDATE table_name
SET column_name = value
WHERE condition;

3. What is the purpose of SET?

SET specifies the columns and new values that should be assigned.

4. Why is WHERE important in UPDATE?

WHERE identifies the rows that should be modified.

5. What happens when WHERE is omitted?

The UPDATE operation can affect every row in the target table.

6. Can UPDATE modify several columns?

Yes. Multiple column assignments can be written in the SET clause.

7. Can UPDATE modify several rows?

Yes. Every row satisfying the WHERE condition can be updated.

8. Can UPDATE use arithmetic expressions?

Yes. Existing values can be used in expressions such as Salary = Salary * 1.10.

9. Can UPDATE use AND and OR?

Yes. Logical operators can be used in the WHERE condition.

10. Can UPDATE use IN?

Yes. IN can select rows whose values match any value in a specified list.

11. Can UPDATE use BETWEEN?

Yes. BETWEEN can select values within a specified range.

12. How is NULL checked in an UPDATE condition?

IS NULL is normally used to identify NULL values.

13. Can UPDATE use CASE?

Yes. CASE can assign different values according to different conditions.

14. Can UPDATE use a subquery?

Yes. Subqueries can supply values or conditions depending on the database system and query design.

15. Can UPDATE work with JOIN?

Yes, although the syntax differs among SQL database products.

16. Is UPDATE a DML command?

Yes. UPDATE is commonly classified as a Data Manipulation Language command.

17. What is a bulk UPDATE?

A bulk UPDATE is an operation that modifies multiple rows as part of one statement or operation.

18. What is COMMIT?

COMMIT confirms changes made within a transaction.

19. What is ROLLBACK?

ROLLBACK cancels uncommitted changes in an active transaction.

20. Can UPDATE affect database performance?

Yes. Large updates may consume CPU, memory, storage, locking, and transaction resources.

21. How can an UPDATE be made safer?

Preview the target rows, use an accurate WHERE condition, test the statement, and use a transaction when appropriate.

22. Does UPDATE create a new row?

No. UPDATE changes values in existing rows.

23. Does UPDATE change table structure?

No. UPDATE changes data values, whereas commands such as ALTER TABLE are used for structural changes.

24. Can UPDATE set a column to NULL?

Yes, provided the column permits NULL values.

25. Why should students learn UPDATE?

UPDATE is a fundamental SQL operation used in database applications, practical projects, examinations, and technical interviews.

Quick Revision of SQL UPDATE

Concept	Important Point
UPDATE	Changes values in existing rows.
SET	Specifies the new column values.
WHERE	Selects the rows to modify.
CASE	Supports condition-based assignments.
Subquery	Can supply values or conditions for an update.
JOIN	Can be used for updates involving related tables, depending on the SQL dialect.
COMMIT	Confirms transaction changes.
ROLLBACK	Reverses uncommitted transaction changes.
Bulk Update	Modifies multiple qualifying rows.

Conclusion

The SQL UPDATE statement provides a direct way to keep existing database information current. It can be used for a small correction in one row, a controlled change across several records, or a larger business operation involving expressions, CASE logic, related tables, and transactions.

The most important skill when working with UPDATE is not simply remembering its syntax. A reliable database developer should understand which rows are being targeted, what values will be assigned, how many records may change, and whether the operation should be protected by a transaction or recovery plan.

Once the basic UPDATE syntax is understood, learners can gradually move toward more advanced operations such as conditional updates, subqueries, UPDATE JOIN techniques, transaction handling, and large-scale data maintenance.

← Previous: DISTINCT Next: DELETE Statement →