Practical 2: Inserting, Updating and Deleting Rows

+---------+----------------+--------+
| book_id | title          | price  |
+---------+----------------+--------+
|       1 | The C Language | 425.00 |
+---------+----------------+--------+

Several columns at once, separated by commas:

UPDATE member
SET city = 'Dombivli', course = 'BSc IT'
WHERE member_id = 5;

SELECT member_id, member_name, course, city FROM member WHERE member_id = 5;

+-----------+-------------+--------+----------+
| member_id | member_name | course | city     |
+-----------+-------------+--------+----------+
|         5 | Neha Patil  | BSc IT | Dombivli |
+-----------+-------------+--------+----------+

The new value may be worked out from the old one, which is how a percentage rise is applied:

UPDATE book SET price = price * 1.10 WHERE subject = 'Databases';

SELECT book_id, title, price FROM book WHERE subject = 'Databases';

+---------+------------------+--------+
| book_id | title            | price  |
+---------+------------------+--------+
|       2 | Database Systems | 792.55 |
|       3 | MySQL Reference  | 548.90 |
|       6 | Learn SQL        | 308.00 |
+---------+------------------+--------+

price = price * 1.10 reads oddly to a programmer and is ordinary in SQL: on the right of the = the column means its current value, and on the left it means where the new value goes. It is applied to every row the WHERE matches, each using its own old price.

The mistake this chapter exists to prevent

CREATE TABLE book_copy AS SELECT * FROM book;

CREATE TABLE ... AS SELECT makes a table with the same columns and the same rows. Now the damage can be done where it does not matter:

UPDATE book_copy SET price = 100.00;

SELECT book_id, title, price FROM book_copy ORDER BY book_id;

+---------+-------------------+--------+
| book_id | title             | price  |
+---------+-------------------+--------+
|       1 | The C Language    | 100.00 |
|       2 | Database Systems  | 100.00 |
|       3 | MySQL Reference   | 100.00 |
|       4 | Data Structures   | 100.00 |
|       5 | Computer Networks | 100.00 |
|       6 | Learn SQL         | 100.00 |
|       7 | Operating Systems | 100.00 |
|       8 | Discrete Maths    | 100.00 |
+---------+-------------------+--------+

Every book now costs 100. There was no WHERE, so every row matched, and MySQL did exactly as it was told without a question or a warning.

There is no undo for that outside a transaction. [Practical 10: COMMIT and ROLLBACK] is the chapter that gives you one, and it is the reason transactions exist.

Three habits, and they cost nothing:

Write the WHERE first. Type WHERE book_id = 1 before you type the SET.

Run it as a SELECT first. SELECT * FROM book WHERE ... with the same condition shows you exactly which rows are about to change. If it returns forty rows and you expected one, you have just saved yourself.

On a real database, START TRANSACTION first. Then a wrong answer is one ROLLBACK away.

DELETE

DELETE FROM book_copy WHERE book_id = 8;

SELECT COUNT(*) AS rows_left FROM book_copy;

+-----------+
| rows_left |
+-----------+
|         7 |
+-----------+

Seven left of eight. DELETE removes whole rows; there is no such thing as deleting one column's value, and setting it to NULL with an UPDATE is what that would mean.

DELETE without a WHERE empties the table, exactly as UPDATE without one changes every row.

A constraint can refuse a delete, and in this database it does:

DELETE FROM book WHERE book_id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`librarydb`.`loan`, CONSTRAINT `loan_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`book_id`))

Book 1 has been borrowed, so a row in loan points at it. Error 1451, from [Practical 2: The Constraints, and What Each One Refuses], and it is the database protecting itself: deleting the book would leave a loan of a book that does not exist.

The three verbs compared

What beginners get wrong

An UPDATE or DELETE with no WHERE. It is the commonest serious mistake in this module.

UPDATE book SET price = 400 WHERE price = NULL. Nothing matches: NULL is never equal to anything, not even to NULL. The test is WHERE price IS NULL.

Quoting numbers, or not quoting text. Text and dates go in single quotes; numbers do not.

Double quotes for a string. MySQL accepts them, the SQL standard does not, and a query written with them may not run on another server. Use single quotes.

Inserting a child row before its parent. A book by author 99 is refused until author 99 exists.

Expecting DELETE to remove a column. It removes rows. Removing a column is ALTER TABLE ... DROP COLUMN.

Thinking UPDATE with no matching rows is an error. It is not; it changes nothing and reports zero rows affected.

Quick revision

• DDL creates and changes structure; DML changes data; DQL asks; DCL grants; TCL commits.
• INSERT INTO t VALUES (...) needs every column in order; INSERT INTO t (cols) VALUES (...) is safer.
• Several rows go in one INSERT, separated by commas, and it is all or nothing.
• INSERT INTO t (cols) SELECT ... fills a table from a query.
• UPDATE t SET col = value WHERE cond; and the new value may use the old one.
• Without a WHERE, UPDATE changes every row and DELETE empties the table.
• Run the WHERE as a SELECT first.
• WHERE col = NULL never matches; use IS NULL.
• Text and dates in single quotes; numbers bare.

What goes in your journal

Aim, then one statement of each kind with the table printed before and after it, so the change is visible. The before and after are the evidence; a journal with only the statements has not shown that they did anything.

Include the refused DELETE with its error, and write one line on what a missing WHERE would have done. That line is the whole safety lesson of the practical.

Test yourself

1. Which family do INSERT, UPDATE and DELETE belong to? DML, the Data Manipulation Language.

2. Why is INSERT INTO t (col1, col2) VALUES (...) better than INSERT INTO t VALUES (...)? Because it does not depend on the number or the order of the table's columns, so it still works when the table changes, and it says which value goes where.

3. What does UPDATE book SET price = 100; do? Sets every book's price to 100, because there is no WHERE to limit it.

4. Why does WHERE price = NULL match nothing? Because NULL is not equal to anything, including another NULL. The correct test is IS NULL.

5. How do you make a copy of a table to practise on? CREATE TABLE copy AS SELECT * FROM original;

6. Your DELETE was refused with error 1451. What does that mean? Another table has rows whose foreign key points at the row you tried to delete, so deleting it would break referential integrity.

7. What is the safest way to check an UPDATE before running it? Run a SELECT with the same WHERE clause and look at how many rows come back.