IB COMPUTER SCIENCE | A3.3.3 Updating Data with SQL

Lesson objective

Explain how INSERT, UPDATE SET and DELETE change records, and how indexed-column modifications affect performance and possible index maintenance.

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 | PKItemNameCategoryQuantityRoom
301Arduino boardElectronics10Lab 1
302Distance sensorElectronics8Lab 1
303Soldering ironTools4Workshop
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;
ItemIDQuantity beforeQuantity after
3011012
30288
30344

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 beforeLogical entry after
Electronics → ItemID 302Sensors → 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

ChangeLogical index implication
Insert Raspberry Pi in ElectronicsAdd an association for the new indexed value and record.
Delete Soldering ironRemove or retire its index association so it is no longer a current match.
Change Category to SensorsReplace the old Category association with the new one.
Change Quantity while Category stays the sameThe 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.

MATCH THE DATA-CHANGE TERM TO ITS ROLE

Terminology

Terminology

DML

Data manipulation language: statements accessing or changing data in existing structures.

INSERT INTO

Adds new records to a table.

Column list

The destination columns explicitly named in an INSERT.

VALUES

Supplies values corresponding to the listed insertion columns.

UPDATE

Changes values in existing matching records.

SET

Specifies the assignments in an UPDATE statement.

Assignment

Storing a specified or calculated new value in a field.

WHERE

Defines which rows satisfy an operation’s condition.

DELETE

Removes matching records while retaining the table definition.

DROP TABLE

A DDL operation removing a table object if permitted.

NULL

An absent or unknown value, distinct from a literal text value.

Default value

A value supplied under the DBMS’s default rules when applicable.

Primary key

The selected unique non-null row identifier.

Foreign key

A declared reference to an appropriate key in a related table.

Constraint

A rule restricting permitted database values or relationships.

CHECK

A constraint condition such as Quantity >= 0.

NOT NULL

A constraint forbidding a NULL field value.

Affected records

Rows targeted by a particular data modification.

Index

An access structure associating indexed values with records.

Indexed column

A column used in an index definition.

Non-unique index

An index permitting several records to share an indexed value.

Index maintenance

DBMS work keeping index information consistent and usable as data changes.

Write overhead

Extra resource work required while modifying data.

I/O

Input/output activity, such as reading or writing storage pages.

Page split

Division of a storage page as an index grows, depending on its design.

Index bloat / inefficient layout

Space use beyond an efficient layout, which can follow certain modification patterns.

Index rebuild

Constructing a fresh index, often to improve layout or address a specific maintenance need.

Index reorganisation

DBMS-specific work improving an existing index layout.

Planner statistics

Summaries of data distributions used to choose query execution plans.

Transaction

A unit of database work governed by transaction rules; commit/rollback behaviour depends on its state and DBMS.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which describe INSERT correctly?
2. Which apply to SET Quantity = Quantity + 2?
3. Which correctly limit an UPDATE?
4. Which distinguish DELETE from clearing Room?
5. Which may reject an INSERT here?
6. Which describe an ordinary index?
7. Which follow changing an indexed Category?
8. Which are reasonable after heavy data modification?
9. Which distinguish statistics and index layout?
10. Which strengthen an explanation?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Explain how INSERT adds the Raspberry Pi record.

2. Explain why a second insert with ItemID 304 fails after the first succeeds.

3. Explain UPDATE Equipment SET Quantity = 12 WHERE ItemID = 301.

4. Explain how to update two columns in the same record.

5. Explain Quantity = Quantity + 2 in a category-targeted UPDATE.

6. Explain the consequence of omitting WHERE from UPDATE or DELETE.

7. Explain how to remove a room value while keeping the equipment record.

8. Explain why changing an indexed column can increase modification cost.

9. Explain why an index can help an UPDATE yet also add work.

10. Explain why significant modifications might lead to rebuilding or reorganisation.

11. Explain why every update does not require a full index rebuild.

12. Explain the difference between rebuilding an index and updating planner statistics.

EXPLAIN IT | EQUIPMENT CHANGES

Explain how the department would add a new equipment record, increase quantities for Electronics items and remove one mistaken record. Write the SQL and link each WHERE condition to its scope. Then explain the extra work when an indexed Category changes, and why later rebuilding or reorganisation might be considered after major modifications.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.3.3 Updating Data with SQL is coming soon. Complete the matching task and written questions in your exercise book.