To illustrate a write transaction with the winestore database, consider an example of inserting a new wine. This process can be performed with the MySQL command-line interpreter. Only one user is interacting with the DBMS in this example.

We can now update the new wine row to set the winery_id=221. However, which row to update? An easy way to find the wine_id of the new wine row is to use the built-in function last_insert_id( ). As discussed in the earlier section Section 3.5, this function returns the number created by the most recent auto_increment modifier:

 SELECT last_insert_id( );

This returns the wine_id of the inserted row:

+------------------+
| last_insert_id( ) |
+------------------+
|             1029 |
+------------------+
1 row in set (0.00 sec)

You can now issue the UPDATE statement:

UPDATE wine SET winery_id = 221 
  WHERE wine_id = 1029;

The third step is to set the variety information for the new wine. We need the variety_id values for Cabernet and Merlot. These can be found with a simple query:

SELECT * FROM grape_variety;

In part, the following results are produced:

+------------+------------+
| variety_id | variety    |
+------------+------------+
|          1 | Riesling   |
|          2 | Chardonnay |
|          3 | Sauvignon  |
|          4 | Blanc      |
|          5 | Semillon   |
|          6 | Pinot      |
|          7 | Gris       |
|          8 | Verdelho   |
|          9 | Grenache   |
|         10 | Noir       |
|         11 | Cabernet   |
|         12 | Shiraz     |
|         13 | Merlot     |

Cabernet has a variety_id=11 and Merlot a variety_id=13. We can now insert two rows into the wine_variety table. Because Cabernet is the first variety, set its ID=1, and ID=2 for Merlot:

INSERT INTO wine_variety 
  SET wine_id=1029, variety_id=11, id=1;
INSERT INTO wine_variety 
  SET wine_id=1029, variety_id=13, id=2;

The final step is to insert the first inventory row into the inventory table for this wine. There are 24 bottles, with a per-bottle price of $14.95 and per-case price of $171.99:

INSERT INTO inventory VALUES (1029, 1, 24, 14.95, 171.99);

We've now completed the process of inserting rows into other tables in the winestore is similar. Adding data to the winery, region, inventory, and orders tables follows the same approach. Insertion of rows into the customer and grape_variety tables is simpler because there are no attributes that require lookups in other tables.

In this example, we consider the steps required to buy a bottle of wine. Again, assume that there is only one user reading or writing data with the DBMS. The complete process—implemented as part of the winestore web database application—is described in Chapter 12.

To motivate this example, consider a customer, Dimitria Marzalla, who has added two bottles of the new De Morton Wines Curry Hill Cabernet Merlot 1996 to her shopping cart and now wishes to purchase the wines.

Before showing you how the purchase is finalized, let's examine the information recorded in the user shopping cart and what we know about the user.

First, we know that cust_id=1 is the ID for this customer and that the wine being purchased has wine_id=1029. This associated information has been previously determined in the process of collecting data for the purchase in the online winestore.

Second, we need to consider how the shopping cart is managed in the winestore. We use the orders and items tables to manage the shopping cart for each user. When a user adds the first item to her shopping cart, a new row is created in the orders table with a dummy cust_id=-1 and the next available order_id for this dummy customer. We use a dummy customer number because customers don't need to log in to add wine to their shopping carts, and because finalized orders are distinguished by having the cust_id of a customer who is a member.

For this example, assume that the shopping cart has order_id=354, and the dummy customer is cust_id=-1. Also assume that the row in the items table that represents the wine in the shopping cart has a cust_id=-1, an order_id=354, an item_id=1, a wine_id=1029, a quantity qty=2, and the price information for the wine. The price is $14.95 per bottle.

Before finalizing an order, we need to determine if there are two bottles of the wine available. A wine can be added to the shopping cart if there is any stock available, but this doesn't necessarily mean that there is more than one bottle left or that another user has not purchased the wine in the meantime. If there is sufficient wine available to finalize an order, we reduce the on-hand stock by two bottles. Checking if there are two bottles available can be done with the following query:

SELECT SUM(on_hand)
  FROM inventory 
  WHERE wine_id=1029;

A GROUP BY wine_id is unnecessary in this case because only one wine is selected.

Assuming there are more than two bottles available, we need to reduce the on-hand stock, beginning with the oldest inventory; this was one of the system requirements defined in Chapter 1. There are several ways to find the oldest inventory and the wine per-bottle price. A simple technique is to inspect the inventories:

SELECT inventory_id,cost,on_hand
  FROM inventory
  WHERE wine_id=1029
  ORDER BY date_added;

The oldest (and only) inventory_id=1, and there is an on-hand stock of 24 bottles. We then reduce the on-hand stock by two:

UPDATE inventory 
  SET on_hand = on_hand - 2
  WHERE wine_id=1029 AND inventory_id=1;

If the on-hand stock in an inventory row is reduced to zero—which isn't so in this case—we then remove that row:

DELETE FROM inventory 
  WHERE wine_id = 1029 AND inventory_id=1;

Other possibilities may also occur, such as having to manipulate two inventories because the oldest inventory has only one bottle left. These possibilities are discussed in further detail in Chapter 12.

Having reserved two bottles of the wine for shipping, we can finalize the order for the customer. To do so, we need to store the details of the shopping cart entries in the orders and items tables. As discussed previously, by tracking the shopping cart of this user we know it has the order_id=354 for the dummy cust_id=-1. We also need to know how many previous orders this customer has made:

SELECT max(order_id) FROM orders WHERE customer_id=1;

If you find the customer previously made two orders, you update the shopping cart order row so that it is now the third order for this customer. Use this statement:

UPDATE orders SET cust_id = 1, 
                  order_id = 3, 
                  date = NULL,
                  delivery = 7.95,
                  discount = 0
  WHERE cust_id = -1 AND order_id = 354;

The shopping cart entry is now a customer order. date=NULL sets the date attribute to be the current system time and date. The delivery cost is $7.95, and there is no discount on the order.

To complete the order, we also update the related items row in the shopping cart, which contains the two bottles of wine. Use the following UPDATE statement:

UPDATE items SET cust_id = 1,
                 order_id = 3,
                 date = NULL
  WHERE cust_id = -1 AND 
        order_id = 354 AND
        item_id = 1;

There is no need to update the wine_id, price, or qty (quantity).

We can now confirm to the customer the purchase of two bottles of Curry Hill and ship the order.

This isn't quite the whole picture of purchasing wines or updating the database. In Chapter 6, we return to similar examples and discuss the implications and problems of many users interacting with the database at the same time.

We'll now show how insertion and querying can be closely tied together with an INSERT INTO ... SELECT statement. This is useful for copying data and, if needed, modifying the data as it is copied.

There are two sensible limitations of the INSERT INTO ... SELECT statement: first, the query can't contain an ORDER BY, and second, the FROM clause can't contain the target table of the INSERT INTO.