Learn
A3.3.3 | UPDATING DATA WITH SQL
01 | THE DATABASE MUST CHANGE WITH THE WORLD
The robotics department receives more boards, moves equipment to a different room and removes a record entered by mistake. A database is useful only if these changes can be recorded accurately.
SQL provides three central data-manipulation operations: INSERT INTO adds records, UPDATE ... SET changes values in existing records and DELETE removes records. In this syllabus title, “update data” refers broadly to maintaining data, not only the SQL UPDATE command.
To explain, connect the command to how it works and the resulting change. For example: “WHERE selects ItemID 301, therefore the new quantity is assigned to that record rather than every item.”
We will also follow a less visible effect: when data changes, an index used to locate that data may need maintenance. Faster searches can come with extra write work.
02 | OUR EQUIPMENT TABLE AND ITS RULES
| ItemID | PK | ItemName | Category | Quantity | Room |
|---|---|---|---|---|
| 301 | Arduino board | Electronics | 10 | Lab 1 |
| 302 | Distance sensor | Electronics | 8 | Lab 1 |
| 303 | Soldering iron | Tools | 4 | Workshop |
CREATE TABLE Equipment (
ItemID INTEGER PRIMARY KEY,
ItemName VARCHAR(60) NOT NULL,
Category VARCHAR(30) NOT NULL,
Quantity INTEGER NOT NULL CHECK (Quantity >= 0),
Room VARCHAR(30)
);
CREATE INDEX idx_equipment_category ON Equipment (Category);One record represents one equipment type held by the department. Quantity is the number of units held; it must be a non-negative whole number. ItemID is the required unique identifier.
The table definition also creates an ordinary, non-unique index on Category. Several items can share Electronics, so this index must not incorrectly prohibit repeated categories.
The definition and index creation are DDL. In the worked changes that follow, the table’s columns stay the same while DML changes its records. SQL syntax and detailed behaviour can vary between DBMSs.
03 | INSERT INTO ADDS A COMPLETE NEW RECORD
INSERT INTO Equipment (ItemID, ItemName, Category, Quantity, Room) VALUES (304, 'Raspberry Pi', 'Electronics', 6, 'Lab 2');
INSERT INTO names the destination table and lists the columns receiving values. VALUES supplies the matching values in that same order: 304 belongs to ItemID, Raspberry Pi to ItemName, and so on.
The text values use single quotes. The numeric ID and quantity are written as numeric literals. The semicolon ends the statement. After successful execution, the table contains one additional record; the original three remain.
Listing columns explicitly makes the intended mapping clear and reduces dependence on the table’s declared column order. If you supply five named columns, the values must match those five positions.
04 | AN INSERT MUST SATISFY THE TABLE RULES
INSERT INTO Equipment (ItemID, ItemName, Category, Quantity) VALUES (305, 'USB cable', 'Electronics', 20);
Room is omitted here. Because this example allows NULL and supplies no default for Room, its value is NULL. NULL means absent or unknown, not an empty string or the literal text “NULL”.
An omitted field with a defined default may receive that default under the DBMS’s rules. Omitting a required field without an applicable supplied/generated/default value causes failure. Do not assume omitted fields always become zero or blank.
The DBMS rejects an insertion violating an enforced constraint, such as a duplicate ItemID, NULL ItemName or negative Quantity. A valid type alone does not prove a value is sensible: INTEGER permits negative numbers, so the CHECK rule adds the non-negative requirement.
05 | UPDATE SET REPLACES VALUES IN EXISTING RECORDS
UPDATE Equipment SET Quantity = 12 WHERE ItemID = 301;
| ItemID | Quantity before | Quantity after |
|---|---|---|
| 301 | 10 | 12 |
| 302 | 8 | 8 |
| 303 | 4 | 4 |
UPDATE names the table. SET specifies the column and new value. WHERE chooses the records to which that assignment applies. Here the unique ItemID condition selects at most one record.
The right-hand 12 becomes the new Quantity for ItemID 301. This does not add another record and does not redefine Quantity’s data type. If no record has ItemID 301, this statement changes no records; it does not automatically insert one.
In SQL assignment syntax, SET Quantity = 12 supplies a new value. Inside WHERE, ItemID = 301 is a comparison selecting rows. Their roles differ even though both use an equals sign.
06 | CHANGE MORE THAN ONE FIELD
UPDATE Equipment SET Quantity = 12, Room = 'Lab 2' WHERE ItemID = 301;
Separate assignments with commas. This statement changes both Quantity and Room for ItemID 301. ItemName and Category remain as they were, because they are not assigned new values.
Do not use AND between SET assignments. AND combines conditions in WHERE; commas separate the column assignments in this example.
A primary-key condition is useful when the requirement identifies one exact record. A name may repeat, so an UPDATE based only on ItemName could affect several records unless uniqueness is guaranteed.
07 | CALCULATE A NEW VALUE FROM THE OLD ONE
UPDATE Equipment SET Quantity = Quantity + 2 WHERE Category = 'Electronics';
Read Quantity = Quantity + 2 as: take the current row’s stored quantity, add two, then store the result back in its Quantity field. It is an assignment, not a mathematical claim that a number equals itself plus two.
Starting from the original dataset, Arduino board changes from 10 to 12 and Distance sensor from 8 to 10. Soldering iron stays at 4 because its Category is Tools. Each matching row uses its own old quantity.
An UPDATE can intentionally affect many rows. “Only one record changes” is true only if the condition and data guarantee it. Repeating an increment statement applies the increment again, so check that repeated execution is actually intended.
08 | WHERE DEFINES THE SCOPE OF A CHANGE
UPDATE Equipment SET Quantity = 0;
Without WHERE, this statement attempts to set every record’s Quantity to zero. It may be valid SQL and satisfy the non-negative check while still being completely wrong for the user’s intention.
Compare a row-specific condition with a group condition: WHERE ItemID = 301 targets one identified item; WHERE Category = 'Electronics' targets all rows in that category.
A useful workflow first selects the target IDs using the intended condition. Then execute the modification with that condition and verify the outcome. In a live multi-user system, a preview is only a snapshot: concurrent changes can require transactions or other suitable coordination.
09 | DELETE REMOVES RECORDS
DELETE FROM Equipment WHERE ItemID = 303;
DELETE FROM identifies the table and WHERE selects the records to remove. This example removes ItemID 303’s whole record, including its name, quantity, category and room. It does not delete just one field value.
The Equipment table and its definition remain. ItemIDs 301 and 302 are unaffected, and the identifier 303 is not reassigned automatically to another existing row.
To clear an optional room value without removing the equipment record, use UPDATE instead:
UPDATE Equipment SET Room = NULL WHERE ItemID = 302;
A DELETE without a matching row removes no records. A DBMS may also reject a deletion if other tables reference the row and the chosen referential action prevents its removal.
10 | EMPTYING A TABLE IS NOT DROPPING IT
DELETE FROM Equipment;
DELETE without WHERE removes all records, subject to permissions, constraints and other DBMS behaviour. Equipment still exists and can receive new records.
DROP TABLE Equipment is different: it is DDL removing the table object if permitted. DELETE is DML operating on the records. Neither should be confused with UPDATE setting Room to NULL, which retains the row.
Whether changes can be rolled back depends on transaction state, autocommit and the DBMS. Do not promise a universal undo after a command has been committed. A later lesson develops transactions in detail.
11 | TRY CHANGES IN AN IN-PAGE MODEL
Choose an operation, preview the intended result, then apply it to the teaching dataset. Compare the record table and the conceptual Category index. Changing the selected operation or quantity clears the pending preview.
For the quantity option, enter a whole number from 0 to 1000. Attempt the insert twice to see a duplicate-key rejection. The model is temporary: it does not connect to a database, execute arbitrary SQL or save data when the page is closed.
The index display is a sorted list of category/ID entries for explanation. It models logical consistency, not a physical B-tree, timing benchmark, page layout, locking or transaction engine.
CURRENT RECORDS
CONCEPTUAL CATEGORY INDEX
12 | WHAT AN INDEX DOES
An index is an additional access structure associating indexed values with matching records. The Category index can help the DBMS locate Electronics items without checking every table row when that access path is worthwhile.
It resembles a book index: the term gives a route to relevant material. A database index, however, is maintained by the DBMS as data changes. It is not a second user-edited copy of the whole table.
A non-unique Category index can contain multiple entries for Electronics. An index can improve suitable searches, including finding rows for UPDATE or DELETE, but maintaining it adds write work and consumes storage. For a small table or a large fraction of rows, a scan may still be preferable.
13 | CHANGING AN INDEXED COLUMN HAS TWO EFFECTS
UPDATE Equipment SET Category = 'Sensors' WHERE ItemID = 302;
| Logical entry before | Logical entry after |
|---|---|
| Electronics → ItemID 302 | Sensors → ItemID 302 |
The table’s Category value changes from Electronics to Sensors. The index must also reflect the new category so later searches find the record under Sensors, not under an obsolete value.
Conceptually, the old key-to-record association is removed or retired and the new association is added. The exact implementation depends on the DBMS, index type and concurrency model; the teaching list is not a physical algorithm.
This extra maintenance requires resources such as CPU, memory and I/O. Therefore changing indexed values can cost more than the corresponding table change with no such index. The benefit is that later suitable searches can still use the correct access structure.
14 | INSERTS, DELETES AND OTHER UPDATES ALSO MATTER
| Change | Logical index implication |
|---|---|
| Insert Raspberry Pi in Electronics | Add an association for the new indexed value and record. |
| Delete Soldering iron | Remove or retire its index association so it is no longer a current match. |
| Change Category to Sensors | Replace the old Category association with the new one. |
| Change Quantity while Category stays the same | The Category key is unchanged; physical work still depends on the DBMS and indexes involved. |
Do not say “every update rebuilds every index”. The DBMS normally maintains affected index information automatically as part of ordinary data changes. Some storage engines and index designs can require work even when the particular indexed key is unchanged.
Additional indexes increase the structures the system may have to maintain. An index on a frequently searched field may justify that cost; an unused index may add storage and write overhead without a useful retrieval benefit.
An index can also help locate the target rows, so the total performance effect is a trade-off: faster identification of records versus the cost of maintaining access structures.
15 | WHY SIGNIFICANT MODIFICATIONS MAY NEED MAINTENANCE
After heavy inserts, updates or deletes, some index structures may use space less efficiently. For example, many deleted entries can leave sparsely used pages; growth can cause page splits and a less compact arrangement. Effects vary by storage engine and index type.
The index can remain logically correct while its physical layout is inefficient. This is different from saying that searches become wrong unless a student manually updates the index after each INSERT.
An administrator may consider a rebuild, which constructs a fresh index, or a DBMS-supported reorganisation, which improves an existing layout. These operations are not identical across products. Some systems reclaim space or perform other maintenance through different mechanisms.
The syllabus asks why indexes might need rebuilding or reorganisation after significant modification. It does not imply that every large update needs an immediate full rebuild.
16 | MAKE MAINTENANCE A MEASURED DECISION
Suppose a large equipment history has had most old records deleted, and its indexes occupy far more space than expected. If monitoring shows inefficient layout and query performance has deteriorated, an appropriate rebuild or reorganisation may improve space use and access efficiency.
That work itself consumes CPU, I/O, temporary storage and time. Depending on the operation and DBMS, it may block activity or require a supported online/concurrent option. Schedule and test it in the real environment.
Also distinguish index layout from query-planner statistics, which summarise data distributions for choosing execution plans. Updating statistics can help the planner after major changes, but it is not the same operation as rebuilding an index.
A good explanation connects cause to effect: “Many deletions left an inefficient index layout, therefore rebuilding may make access more compact. However, it uses resources and may disrupt access, so maintenance should follow monitoring rather than run after every changed row.”
17 | CHECK THE OPERATION AND ITS CONSEQUENCE
Choose the best explanation for each requirement. The first checked response counts, and you can restart for another attempt.
18 | EXPLAIN THE WHOLE CHANGE
For a command question, identify the target records, the action and the outcome. For example: “UPDATE sets Quantity to 12 for the row whose ItemID is 301. Because the WHERE condition uses the unique ID, other item rows keep their previous values.”
For an index question, include both the search benefit and the write consequence. Explain that the DBMS maintains the relevant index associations automatically, while later physical maintenance may be useful after substantial change.
Before finishing, check column/value order in INSERT, commas in SET, complete WHERE conditions, valid constraints and the difference between removing a row and clearing a value. Then verify the actual affected records rather than relying only on a successful SQL message.
Further reading: PostgreSQL index introduction and index maintenance considerations.