1.5. Modifying data

This chapter will explain the basic mechanisms for adding data to tables, removing data from tables, and modifying data.

1.5.1. Tables used in this chapter

For this chapter, we will work with the tables bookstore_inventory and bookstore_sales, which simulate a simple database that a seller of used books might use. The bookstore_inventory table lists printed books that the bookstore either has in stock or has sold recently, along with the condition of the book and the asking price. The column stock_number is a unique identifier for each book.

When the bookstore sells a book, a record is added to the bookstore_sales table. This table lists the stock_number of the book sold, the date sold, and the type of payment. The column receipt_number is a unique identifier for the sale. (This may not be a very good database design; it assumes we only sell one book at a time!)

A full description of these tables can be found in Appendix A.

1.5.2. Adding data using INSERT

To add rows to a table in the database, we use a statement starting with the keyword INSERT. In its simplest form, INSERT lets you add a single row to a table by providing a value for each column in the table as defined. As an example, suppose a customer at our bookstore purchases our copy of One Hundred Years of Solitude by Gabriel García Márquez. This book is listed in our inventory with a stock number of 1455. The customer purchases the book on August 14, 2021 and pays cash. Finally, we provide a receipt to the customer with receipt number 970.

In the database, the table bookstore_sales is defined with these columns: receipt_number, stock_number, date_sold, and payment.

We could record the sale using this statement:

Try the above statement in the interactive tool, then use a SELECT query to verify that the new data has been added. Note that the order in which the values are listed matches the order in which columns are defined for the bookstore_sales table.

1.5.2.1. Specifying columns

Performing the insert as we did above works fine when we know for certain how a table has been defined in a database. However, tables change over time in practice, which may result in columns appearing in a different order, or in more columns being added to the table. If this happens, old SQL code that makes assumptions about the table structure will break. So it is a better practice to provide not only the data, but the names of the columns in which we want to put the data. To do this, we simply list the column names in parentheses after the table name:

INSERT INTO bookstore_sales (receipt_number, stock_number, date_sold, payment)
VALUES (971, 1429, '2021-08-15', 'trade in');

As described in Chapter 1.6, it is possible to have some of our columns be automatically generated by the database. For example, if we add a new book to our inventory, we want to generate a new, unique stock number. The bookstore_inventory table is set up to do this. When the database generates values like this for us, we should not provide a value for the generated column. Specifying column names lets us insert data for only the non-generated columns.

The bookstore_sales table is likewise set up to generate unique receipt_number values; above we provided values for the receipt number, which only works as long as the values we provide are not already used. The bookstore_sales table also has a default setting for the date_sold column - it will put in today’s date for you if you do not provide a value for the column. Here is how the bookstore_sales table might be used in practice:

INSERT INTO bookstore_sales (stock_number, payment)
VALUES (1460, 'cash');

1.5.2.2. Inserting multiple rows

While it is perfectly valid to do multiple INSERT statements to add multiple rows of data, SQL also lets you provide multiple rows in a single INSERT statement. Perhaps we wish to enter all of a day’s sales in one statement. We can enter this query:

INSERT INTO bookstore_sales (stock_number, payment)
VALUES
  (1444, 'credit card'),
  (1435, 'cash'),
  (1453, 'credit card')
;

(Note for Oracle users: Oracle does not permit multiple rows in an INSERT.)

1.5.2.3. Inserting query results

SQL also provides the capability of providing values via a SELECT query. As a somewhat contrived example, suppose we create another table named bookstore_recent_sales with columns named author and title. We will store data in this table about books we sold recently (perhaps to see what books and authors are popular, to inform our purchasing). We might want to fill this table with the unique books that have been sold in the past month.

The syntax is the same as a regular INSERT, but with the VALUES clause replaced by a SELECT query (which must return columns of the same type and in the same order as the columns we are inserting into). Try the statements below to see this in action.

CREATE TABLE bookstore_recent_sales (author TEXT, title TEXT);

INSERT INTO bookstore_recent_sales (author, title)
SELECT DISTINCT i.author, i.title
FROM
  bookstore_inventory AS i
  JOIN bookstore_sales AS s ON s.stock_number = i.stock_number
WHERE s.date_sold BETWEEN '2021-08-01' AND '2021-08-31';

1.5.3. Removing data with DELETE

Removing rows from a table is accomplished using DELETE statements. DELETE statements are generally very simple, requiring only a FROM clause and optionally a WHERE clause. You can delete data from only one table at a time. As an example, if we want to remove all sales from bookstore_sales prior to August 1, 2021, we could write:

This is probably a bad idea unless we first delete the data from bookstore_inventory for the books we are deleting - otherwise we might think that we still have those sold books. Since we cannot delete data from multiple tables in one query (e.g., using a join) it may be tricky to see how to get rid of the appropriate rows from bookstore_inventory. The information about what rows we want to delete is actually in bookstore_sales (in the date_sold column). The technique we need will be covered in Chapter 1.8 - using a subquery. Here is the necessary query, given without explanation for now:

DELETE FROM bookstore_inventory
WHERE stock_number IN
  (SELECT stock_number FROM bookstore_sales
   WHERE date_sold < '2021-08-01')
;

In Chapter 1.7 we will discuss other techniques for keeping multiple tables consistent with each other.

If the WHERE clause is omitted in a DELETE query, then all data from the table is removed.

As with any data modification statement, the effects of a DELETE statement are immediate and permanent. To some extent, you can undo the result of an INSERT with a DELETE if you know which rows you inserted; however, it is impossible to restore deleted rows unless you have a backup of the data. Thus, it is very important to be sure you are deleting only what you want to delete. A simple way to test this before you perform a delete is to replace DELETE with SELECT * in your statement - this will show you exactly the rows that your statement would delete.

Remember that with our interactive examples, any changes you make to this book’s database only last for the current viewing session, so if you wish to restore the deleted data, you may do so by refreshing the page in your browser.

1.5.4. Modifying data with UPDATE

One of the most powerful capabilities SQL provides is data modification using UPDATE statements. The form of an UPDATE is:

UPDATE tablename
SET
  column1 = expression1,
  column2 = expression2,
  ...
[WHERE expressions]
;

Often, we may want to update a single row in our database. For example, perhaps we examine one of the books in our bookstore inventory and decide that its condition is better than we initially thought. Our copy of Slow River by Nicola Griffith (stock number 1460) is listed as in fair condition, with a price of 2 (in some unit of currency). We want to upgrade the condition to “good” and raise the price to 2.50 at the same time:

We can also update multiple rows at a time. Perhaps we mistakenly put in all sales for August 1, 2021 as July 31 instead. We can fix these in one query:

UPDATE bookstore_sales
SET date_sold = '2021-08-01'
WHERE date_sold = '2021-07-31';

Of course, this only works if none of the sales marked as July 31 were correct; we might have to be more clever with our WHERE clause if not.

The real power of UPDATE, though, is that the right hand side of the assignments in the SET clause can be expressions, and these expressions are based on the row being updated. Hence, we can do something like the following:

UPDATE bookstore_inventory
SET price = price + 0.25;

This would raise the price of every book by 0.25.

1.5.5. Other data modification statements

SQL provides some other data modification statement types, which may or may not be supported in your database. TRUNCATE TABLE removes all rows from a table, and is typically faster than DELETE (but can only be used to remove all rows). MERGE is a somewhat complex operation that combines inserts, updates, and deletes, allowing synchronization of a table with another table or join of tables. Neither of these operations is strictly necessary, given that the same results can be accomplished with INSERT, UPDATE, and DELETE. We will not cover them further in this book.

1.5.6. Self-check exercises

This section contains exercises on INSERT, UPDATE, and DELETE, using the bookstore_inventory and bookstore_sales tables. Keep in mind that the database we are using for these exercises is shared with the interactive examples above, so any changes you have applied in an interactive tool above are reflected in the database you use below. If the results you get are not what you are expecting, you may need to reload this page in your browser to get a fresh copy of the database.

If you get stuck, click on the “Show answer” button below the exercise to see a correct answer.

Write a statement to add the book House Made of Dawn by N. Scott Momaday to the bookstore_inventory table. Use 1471 for the stock number, ‘like new’ for the condition, and 4.75 for the price.

Write a statement to add all books by John Steinbeck (from our books table) into bookstore_inventory with a condition of ‘new’ and a price of 4.00. Note that there is no good way to provide unique stock numbers for each of these books, but if you omit the stock_number column entirely, the bookstore_inventory table is set up to provide unique values automatically.

Write a statement to remove all books from bookstore_inventory that are in ‘fair’ condition.

Write a statement to change the payment type to ‘cash’ for the sale with receipt number 963.

Write a statement to set the price (in our bookstore inventory) for all books by Clifford Simak to a special sale price of 1.0.

Write a statement to double the price of all books in ‘new’ condition.

❏

You have attempted of activities on this page