Learning MySQL and MariaDB

Chapter 8. Updating and Deleting Data

Data in databases will change often. There’s always something to change, some bit of information to add, some record to delete. For these situations in which you want to change or add pieces of data, you will mostly use the UPDATE statement. For situations in which you want to delete an entire row of data, you’ll primarily use the DELETE statement. Both of these SQL statements are covered extensively in this chapter.

Updating Data

The UPDATE statement changes the data in particular columns of existing records. The basic syntax is the UPDATE keyword followed by the table name, then a SET clause. Generally you add a WHERE clause so as not to update all of the data in a given table. Here is a generic example of this SQL statement:

UPDATE table
SET column = value, ... ;

This syntax is similar to the emphatic version of the INSERT statement, which also uses the SET clause. There isn’t a less emphatic syntax for UPDATE, as there is with INSERT. An important distinction is that there is no INTO clause. Instead, the name of the affected table is just given immediately after the UPDATE keyword.

Let’s look at an example of the UPDATE statement. In Chapter 5, we created a database called birdwatchers and a table within it called humans that would contain data about people who watch birds and use the rookery site. We then entered information on some of those people. In one of the exercises at the end of Chapter 5, we added a column (country_id) which contains ...