A3.3.3 · Database programming
Changing data with SQL
By the end you can
Explain INSERT, UPDATE and DELETE, what can go wrong without a WHERE, and what indexes cost.
A database that could only be read would be a book. SQL has three statements to change the data: INSERT adds new rows, UPDATE changes existing ones and DELETE removes them.
INSERT, UPDATE, DELETE#
- INSERT INTO table (columns) VALUES (values); Text goes in quotes, numbers do not, and the values must be in the same order as the columns. Name the columns if you are not giving them all: the rest become NULL (or fail if they are NOT NULL).
- UPDATE table SET column = value WHERE condition; The value can be a calculation using the current value:
Price = Price * 1.1. - DELETE FROM table WHERE condition; removes matching rows (the table stays).
The is the one to watch. Leave it out and the statement applies to every row. The sandbox below will happily wipe your Purchase table if you ask it to; real databases are less forgiving, which is why changes are often made inside a transaction (A3.3.6) that can be rolled back.
The database defends its data#
When you change data, the keys and constraints from A3.2.4 are checked. A duplicate primary key, a foreign key that matches nothing, a missing NOT NULL value or a wrong type makes the statement fail, and nothing is changed. Deleting a row that other rows still point at is refused too, unless the foreign key says ON DELETE CASCADE.
Author
| AuthorIDPK | Name | Country |
|---|---|---|
| 1 | Maya Okafor | Nigeria |
| 2 | Liam Chen | China |
| 3 | Sofia Rossi | Italy |
| 4 | Arun Patel | India |
| 5 | Noor Haddad | Egypt |
5 rows. AuthorID INT, Name TEXT(40), Country TEXT(20).
Indexes#
Finding one row among millions by reading every row would be painfully slow. An is a separate lookup structure, like the index at the back of a book: it keeps the values of a column in order, with pointers to the rows. Primary keys are indexed automatically, and you can add more with CREATE INDEX idx_title ON Book (Title).
The price is paid on every change. Each INSERT, UPDATE and DELETE must also update the index, and the index takes up space. So indexes suit columns that are searched a lot (like titles or customer IDs) in tables that are read more than written. Updating an indexed column is slower than updating one that is not. Batching changes, and rebuilding indexes now and then, reduces the cost.
Your turn#
Add a new customer: ID 6, name 'Fay Kim', city 'Seoul'.
A new student joins the school.
A customer moves to a new city.
A product is discontinued and removed from the table.
Searches on the Title column are slow on a table of ten million books.
0 of 4 matched
Check yourself
Changing data
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 13 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Construct an SQL statement to add a customer with CustomerID 14, name 'Jordan Reyes' and city 'Leeds' to the table Customer(CustomerID, Name, City).
[3]Construct: Draw or write it out (a table, a diagram, a program).
2.Construct an SQL statement to raise the price of every item made by the artist with ArtistID 101 by 10% in the table Item(ItemID, ItemDesc, ItemCost, ArtistID).
[3]Construct: Draw or write it out (a table, a diagram, a program).
3.Explain the danger of running UPDATE Item SET ItemCost = 5; or DELETE FROM Item; with no WHERE clause.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
4.Explain the effect of an index on queries and on updates.
[4]Explain: Give reasons: say what happens AND why, or how one thing causes another.
In a nutshell
- INSERT INTO table (columns) VALUES (…) adds a row. UPDATE table SET column = value WHERE condition changes rows. DELETE FROM table WHERE condition removes rows.
- Always check the WHERE clause. Without one, UPDATE and DELETE affect every row.
- The database enforces keys when data changes: a duplicate primary key, a foreign key pointing at nothing, or deleting a row that others refer to is refused.
- An index speeds up lookups on a column like the index of a book, but each change to the data also has to update the index.
- Updates in a transaction can be rolled back; outside one they are saved straight away.