A3.3.3 · Database programming

Updating data in a database

A plant nursery takes a delivery, drops its prices, and discontinues a line. Nothing about the table's design changes, only the rows inside it, and SQL has exactly three statements for that.

Core for SL & HL Command term: explain Builds on A3.3.1

Explainer

A3.3.1 split SQL into DDL (statements that define the structure) and DML (statements that work with the data inside it). SELECT, from A3.3.2, is the DML statement that only reads. This statement is about the three DML statements that actually change what is stored.

An update query is any DML statement that changes the contents of a table rather than just reading them. There are three: INSERT INTO adds new records, UPDATE ... SET modifies existing records, and DELETE removes records.

None of them touch the table's structure. The columns, data types and keys defined by DDL stay exactly as they were, only the rows change.

The three statements

Statement	What it does	Example
INSERT INTO	Adds one new record. The value list must line up with the field list, in the same order.	INSERT INTO Seedling (SeedlingID, SpeciesName, TrayCode, StockCount, Price) VALUES ("S5", "Sea Holly", "T-11", 60, 6.00);
UPDATE ... SET	Changes values in records that already exist. SET names each field and its new value; WHERE decides which rows are affected.	UPDATE Seedling SET Price = 3.00 WHERE SeedlingID = "S3";
DELETE	Removes whole records. The row disappears; individual fields cannot be deleted this way.	DELETE FROM Seedling WHERE StockCount = 0;

WHERE is what limits the damage. UPDATE and DELETE apply to every row that matches the condition. If the condition is left out entirely, every row in the table matches.

DELETE FROM Seedling; is valid SQL. It empties the table. UPDATE Seedling SET Price = 3.00; is also valid, and it reprices every product in the nursery. Neither one produces an error message first.

Worked example

SEEDLING: one table, three updates

A small nursery keeps one table of what is currently growing. Click through and watch each statement change it.

Step 1 of 5 Starting table

Explainer

Why updating an indexed column costs more

Every statement above looks like a single operation. On an indexed column, it is not.

An index is a separate, sorted structure the database keeps alongside a table so that searches on one particular column do not have to scan every row. The nursery's staff search by species name constantly, so SpeciesName is indexed.

The index is a second copy of that column's values, held in order, each pointing back at the row it came from. It is a real object stored on disk, not a setting.

What an update actually does to the index

Follow the same nursery table, this time watching the index rather than the rows.

Step 1 of 4 The index

Fragmentation is what a heavily updated index turns into: the entries are still in the right order, but they are spread across far more space than they need, with a gap left behind wherever an old entry was removed. The index still gives correct answers, it just takes more reads to get them, so the very queries it was created to speed up start slowing down.

How this is managed in practice

Batch the updates. Applying two hundred price changes as one operation makes the index do its maintenance work once, instead of two hundred separate times.

Rebuild or reorganize the index regularly. Reorganizing tidies the existing index in place; rebuilding discards it and constructs a fresh, compact one. Either way the fragmentation left behind by heavy modification is cleared out, which is why this is scheduled after any significant bulk change.

Use partial or filtered indexes. Index only the rows that are actually searched, for example just the species still in stock. A smaller index means less to maintain on every write.

There is a second cost that is easy to miss. While the index is being updated, the rows involved can be locked, so other transactions that need those records have to wait. A slow write does not only slow itself down, it holds up everything queued behind it.

Try it yourself

Change the data yourself

A live copy of the SEEDLING table. Your INSERT, UPDATE and DELETE statements really do change it, and it stays changed until you reset it. That includes the mistakes. SELECT works here too, so you can check the damage before undoing it.

SeedlingSeedlingID, SpeciesName, TrayCode, StockCount, Price
4 rows

SQL statement

Ctrl + Enter also runs

Run the last chip. "Forget the WHERE" is the mistake the warning card near the top of this page describes, and it is much more convincing to watch every price in the nursery become 3.00 than to be told it would. Nothing asks you to confirm, and nothing warns you first, which is exactly the point. Then reset the tables and try DELETE FROM Seedling; on its own.

Check your understanding

SEEDLING has an index on SpeciesName. Which statement forces the database to do extra write work inside the index itself?

Common mistake

Markers reported this pattern for A3.3.3

RecurringThe command term here is explain, which needs a mechanism, not a claim. Answers about indexed columns often stop at "it slows the update down" and stop there. That sentence states the effect and never says what the database is doing extra, so it cannot be given the marks reserved for the reason.

What a weak answer looks like: "Updating an indexed column is slower because indexes make updates take longer." This restates the question. No mechanism, no marks beyond the first.

✓

What it should say: "The row is written, and then the index must be maintained too: the old entry is removed and a new one inserted in sorted position. Repeated modification leaves the index fragmented, so it is rebuilt or reorganized to restore performance." One sentence for the extra work, one for the consequence.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]Construct an SQL statement that adds a new seedling record to the SEEDLING table.

Question 2 · 3 marks

[3]Explain what would happen if the WHERE clause were omitted from an UPDATE statement.

Question 3 · 5 marks

[5]Explain the performance implications of frequently updating an indexed column, and outline one way a database administrator can reduce them.

Flashcards

Active recall for this statement's key terms.