When updating data in MySQL, the most important thing is to have a unique reference of the row that you want to update. For this, primary keys are very useful; however, if you have a table with no primary keys, which should not be the case most of the time, you can still update the rows based on other fields. Other than the reference, you will need the new value and, of course, the table name and field to update. Let's take a look at a very simple example:

mysql> UPDATE book SET price = 12.75 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In this UPDATE query, we set the price of the book with the ID 2 to 12.75. The SET clause does not need to specify only one change; you can specify several changes on the same row as soon as you separate them by commas—for example, SET price = 12.75, stock = 14. Also, note the WHERE clause, in which we specify which rows we want to change. MySQL gets all the rows of this table based on these conditions as though it were a SELECT query and apply the change to this set of rows.

What MySQL will return is very important: the number of rows matched and the number of rows changed. The first one is the number of rows that match the conditions in the WHERE clause. The second one specifies the amount of rows that can be changed. There are different reasons not to change a row—for example when the row already has the same value. To see this, let's run the same query again:

mysql> UPDATE book SET price = 12.75 WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The same row now says that there was 1 row matched, as expected, but 0 rows were changed. The reason is that we already set the price of this book to 12.75, so MySQL does not need to do anything about this now.

As mentioned before, the WHERE clause is the most important bit in this query. Way too many times, we find developers that run a priori innocent UPDATE queries end up changing the whole table because they miss the WHERE clause; thus, MySQL matches the whole table as valid rows to update. This is usually not the intention of the developer, and it is something not very pleasant, so try to make sure you always provide a valid set of conditions. It is good practice to first write down the SELECT query that returns the rows you need to edit, and once you are sure that the conditions match the desired set of rows, you can write the UPDATE query.

However, sometimes, affecting multiple rows is the intended scenario. Imagine that we are going through tough times and need to increase the price of all our books. We decide that we want to increase the price by 16%, which is the same as the current price times 1.16. We can run the following query to perform these changes:

mysql> UPDATE book SET price = price * 1.16;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 0

This query does not contain any WHERE clause as we want to match all our books. Also note that the SET clause uses the price field to get the current value for the price, which is perfectly valid. Finally, note the number of rows matched and changed, which is 8—the whole set of rows for this table.

To finish with this subsection, let's consider how we can use UPDATE queries from PHP through PDO. One very common scenario is when we want to add copies of the already existing books to our inventory. Given a book ID and an optional amount of books—by default, this value will be 1—we will increase the stock value of this book by these many copies. Write this function in your init.php file:

function addBook(int $id, int $amount = 1): void {
    $db = new PDO(
        'mysql:host=127.0.0.1;dbname=bookstore',
        'root',
        ''
    );

    $query = 'UPDATE book SET stock = stock + :n WHERE id = :id';
    $statement = $db->prepare($query);
    $statement->bindValue('id', $id);
    $statement->bindValue('n', $amount);

    if (!$statement->execute()) {
        throw new Exception($statement->errorInfo()[2]);
    }
}

There are two arguments: $id and $amount. The first one will always be mandatory, whereas the second one can be omitted, and the default value will be 1. The function first prepares a query similar to the first one of this section, in which we increased the amount of stock of a given book, then binds both parameters to the statement, and finally executes the query. If something happens and execute returns false, we will throw an exception with the content of the error message from MySQL.

This function is very useful when we either buy more stock or a customer returns a book. We could even use it to remove books by providing a negative value to $amount, but this is very bad practice. The reason is that even if we forced the stock field to be unsigned, setting it to a negative value will not trigger any error, only a warning. MySQL will not set the row to a negative value, but the execute invocation will return true, and we will not know about it. It is better to just create a second method, removeBook, and verify first that the amount of books to remove is lower than or equal to the current stock.

One tricky thing to manage when updating or deleting rows is when the row that we update is part of a foreign key somewhere else. For example, our borrowed_books table contains the IDs of customers and books, and as you already know, MySQL enforces that these IDs are always valid and exist on these respective tables. What would happen, then, if we changed the ID of the book itself on the book table? Or even worse, what would happen if we removed one of the books from book, and there is a row in borrowed_books that references this ID?

MySQL allows you to set the desired reaction when one of these scenarios takes place. It has to be defined when adding the foreign key; so, in our case, we will need to first remove the existing ones and then add them again. To remove or drop a key, you need to know the name of this key, which we can find using the SHOW CREATE TABLE command, as follows:

mysql> SHOW CREATE TABLE borrowed_books G
*************************** 1. row ***************************
       Table: borrowed_books
Create Table: CREATE TABLE `borrowed_books` (
  `book_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime DEFAULT NULL,
  KEY `book_id` (`book_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `borrowed_books_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`),
  CONSTRAINT `borrowed_books_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The two foreign keys that we want to remove are borrowed_books_ibfk_1 and borrowed_books_ibfk_2. Let's remove them using the ALTER TABLE command, as we did before:

mysql> ALTER TABLE borrowed_books
    -> DROP FOREIGN KEY borrowed_books_ibfk_1;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE borrowed_books
    -> DROP FOREIGN KEY borrowed_books_ibfk_2;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Now, we need to add the foreign keys again. The format of the command will be the same as when we added them, but appending the new desired behavior. In our case, if we remove a customer or book from our tables, we want to remove the rows referencing these books and customers from borrowed_books; so, we need to use the CASCADE option. Let's consider what they would look like:

mysql> ALTER TABLE borrowed_books
    -> ADD FOREIGN KEY (book_id) REFERENCES book (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE,
    -> ADD FOREIGN KEY (customer_id) REFERENCES customer (id)
    -> ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Note that we can define the CASCADE behavior for both actions: when updating and when deleting rows. There are other options instead of CASCADE—for example SET NULL, which sets the foreign keys columns to NULL and allows the original row to be deleted, or the default one, RESTRICT, which rejects the update/delete commands.

Deleting data is almost the same as updating it. You need to provide a WHERE clause that will match the rows that you want to delete. Also, as with when updating data, it is highly recommended to first build the SELECT query that will retrieve the rows that you want to delete before performing the DELETE command. Do not think that you are wasting time with this methodology; as the saying goes, measure twice, cut once. Not always is it possible to recover data after deleting rows!

Let's try to delete a book by observing how the CASCADE option we set earlier behaves. For this, let's first query for the existing borrowed books list via the following:

mysql> SELECT book_id, customer_id FROM borrowed_books;
+---------+-------------+
| book_id | customer_id |
+---------+-------------+
|       1 |           1 |
|       4 |           1 |
|       4 |           2 |
|       1 |           2 |
+---------+-------------+
4 rows in set (0.00 sec)

There are two different books, 1 and 4, with each of them borrowed twice. Let's try to delete the book with the ID 4. First, build a query such as SELECT * FROM book WHERE id = 4 to make sure that the condition in the WHERE clause is the appropriate one. Once you are sure, perform the following query:

mysql> DELETE FROM book WHERE id = 4;
Query OK, 1 row affected (0.02 sec)

As you can note, we only specified the DELETE FROM command followed by the name of the table and the WHERE clause. MySQL tells us that there was 1 row affected, which makes sense, given the previous SELECT statement we made.

If we go back to our borrowed_books table and query for the existing ones, we will note that all the rows referencing the book with the ID 4 are gone. This is because when deleting them from the book table, MySQL noticed the foreign key reference, checked what it needed to do while deleting—in this case, CASCADE—and deleted also the rows in borrowed_books. Take a look at the following:

mysql> SELECT book_id, customer_id FROM borrowed_books;
+---------+-------------+
| book_id | customer_id |
+---------+-------------+
|       1 |           1 |
|       1 |           2 |
+---------+-------------+
2 rows in set (0.00 sec)