A3.3.3 Explain how SQL can be used to update data in a database.

📚 You can find additional information related to these learning standards in the course companion pages 204 to 206

The Big Idea

SQL is not just a language for reading data—it's also a powerful tool for modifying data within relational databases. SQL allows you to:

• Insert new records,
• Update existing records,
• Delete records.

These operations are collectively referred to as Data Manipulation Language (DML) statements. However, modifying data—especially in indexed columns—can have serious performance implications, such as slowing down queries or requiring index reorganization.

This article explains how SQL is used to modify data and how these actions interact with indexes behind the scenes. It would be an especially good idea to clearly understand database indexes prior to investigating this topic. 

---

1. Inserting Data with INSERT INTO

Purpose

Adds new rows to a table.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

INSERT INTO students (student_id, name, grade_level)
VALUES (101, 'Alice Smith', 11);

Notes

• You must provide values that match the column data types.
• If a column has a NOT NULL constraint and no default, you must provide a value.
• Auto-incremented keys (like id) can often be omitted.

---

2. Modifying Data with UPDATE ... SET

Purpose

Changes values in existing rows based on a filter condition.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE students
SET grade_level = 12
WHERE student_id = 101;

Important

• Without a WHERE clause, all rows will be updated.
• Updates can cascade performance issues if large portions of a table are modified.

---

3. Removing Data with DELETE

Purpose

Deletes one or more rows from a table.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM students
WHERE grade_level = 9;

Important

• DELETE respects constraints and triggers (e.g., ON DELETE CASCADE).
• Without a WHERE clause, all rows will be deleted—use carefully.

---

4. Performance Implications of Updates on Indexed Columns

Indexes are used to speed up SELECT queries by maintaining a separate, optimized data structure (e.g., B-tree, hash) that maps key values to rows. But when you update or delete rows, especially in indexed columns, this affects index maintenance.

When indexes are impacted

• Inserting into indexed columns: The index must be updated with the new key.
• Updating an indexed column: The old index entry must be removed and a new one added.
• Deleting rows: Index entries must also be deleted.

Performance Concerns

Operation	Effect on Index
INSERT	Adds new entry to the index
UPDATE (on key)	Removes old key, inserts new key — may fragment the index
DELETE	Removes index entry — can leave gaps or imbalance

These modifications may cause the index to become fragmented, reducing its efficiency.

---

5. Index Maintenance: Rebuilding and Reorganizing

After heavy data changes, indexes may require manual intervention:

a. Rebuilding an Index

• Re-creates the entire index structure.
• More expensive operation.
• Restores optimal performance and removes fragmentation.

-- Example (SQL Server syntax)
ALTER INDEX index_name ON table_name REBUILD;

b. Reorganizing an Index

• Defragments the index in-place (less resource-intensive).
• Used for moderate fragmentation.

ALTER INDEX index_name ON table_name REORGANIZE;

Database engines like PostgreSQL use the VACUUM and REINDEX commands for similar purposes.

---

Summary

SQL Commands for Data Modification

Action	Command
Insert	INSERT INTO ... VALUES (...)
Modify	UPDATE ... SET ... WHERE ...
Remove	DELETE FROM ... WHERE ...

Key Concepts

• Always use WHERE clauses to target specific rows when modifying data.
• Be cautious when modifying or deleting data on indexed columns, as it can degrade query performance.
• Index maintenance (rebuilding or reorganizing) may be necessary after bulk operations to restore performance.

---

Conclusion

SQL gives you precise control over inserting, updating, and deleting records—but these operations have side effects on indexing and performance. Understanding how DML operations interact with indexes is crucial for maintaining a high-performance relational database system. In production environments, efficient transaction planning, index monitoring, and batch processing strategies are vital for scalable and safe data updates.