Data Modification

The second time the register interacts with the database, when the inventory number is updated, requires a little more work than simply asking the database for a couple numbers. Now, in addition to requesting the inventory number with a SELECT statement, an UPDATE statement is used to change the value of the number.

First, the register asks the database how many iPods are in the inventory (or “on hand”).

SELECT onhand FROM products WHERE id = 885909054336;

The database returns the number of products on hand, the register decrements that number by one to represent the iPod that was just sold, and then the register updates the database with the new inventory number.

UPDATE products SET onhand = 1471 WHERE id = 885909054336;

This sequence is presented in Figure 4.

In Figure 4, the database responds to the UPDATE query with UPDATE 1, which simply means one record was updated successfully.

Now that the number of iPods on hand has been changed, how does one verify the new number? With another SELECT query, of course! This is shown in Figure 5.

Now, the register has updated the database to reflect the iPod you just purchased and verified the new number of iPods on hand. That was pretty simple, wasn’t it?

More on Databases

You now know databases are made of tables, which are, in turn, made of records. Each record has values for specific columns, and in many cases, a record can be uniquely identified by the value contained in at least one column.

In our example, the barcode number uniquely identified the iPod, which cost $200, in the products table. You have also seen that values in a database can be modified. In this case, the number of iPods on hand was changed from 1,472 to 1,471.