10. Inserting, Updating, and Deleting Rows

Sections
Displaying Table Definitions
Inserting Rows with INSERT
Updating Rows with UPDATE
Deleting Rows with DELETE

To this point, I’ve explained how to use SELECT to retrieve and analyze the data in tables. In this chapter, I’ll explain how to use SQL statements to modify table data:

These statements don’t return a result, but your DBMS normally will print a message indicating whether the statement ran successfully and, if so, the number of rows affected by the change. To see the actual effect the statement had on a table, use a SELECT statement.

Unlike SELECT, which only accesses data, these statements change data, so your database administrator might need to grant you permission to run them.

Displaying Table Definitions

To use INSERT, UPDATE, or DELETE, you must know about the columns of the table whose data you’re modifying, including:

Table definitions of the sample-database tables are given in “The Sample Database” in Chapter 2, but you can get the same information by using DBMS tools that describe database objects. This section explains how to use those tools to display table definitions for the current database.

To display table definitions in Microsoft Access:

To display table definitions in Microsoft SQL Server:

  1. Start SQL Server Management Studio or the interactive sqlcmd command-line tool (see “Microsoft SQL Server” in Chapter 1).

    The sqlcmd command displays pages that speed by. It’s easier to use the graphical tools and choose Query > Results to Grid.

  2. Type sp_help table.

    table is a table name.

  3. In SQL Server Management Studio, choose Query > Execute or press F5 (Figure 10.2).

    or

    In sqlcmd, press Enter, type go, and then press Enter.

    Figure 10.2Displaying a table definition in Microsoft SQL Server.

    Screenshot: A table definition in Microsoft SQL Server

To display table definitions in Oracle Database:

  1. Start the interactive sqlplus command-line tool (see “Oracle Database” in Chapter 1).
  2. Type describe table; and then press Enter (Figure 10.3).

    table is a table name.

    Figure 10.3Displaying a table definition in Oracle Database.

    Screenshot: A table definition in Oracle Database

To display table definitions in IBM Db2 Database:

  1. Start the db2 command-line processor (see “IBM Db2 Database” in Chapter 1).
  2. Type describe table table; and then press Enter (Figure 10.4).

    table is a table name.

    Figure 10.4Displaying a table definition in IBM Db2 Database.

    Screenshot: A table definition in IBM Db2 Database

To display table definitions in MySQL:

  1. Start the interactive mysql command-line tool (see “MySQL” in Chapter 1).
  2. Type describe table; and then press Enter (Figure 10.5).

    table is a table name.

    Figure 10.5Displaying a table definition in MySQL.

    Screenshot: A table definition in MySQL

To display table definitions in PostgreSQL:

  1. Start the interactive psql command-line tool (see “PostgreSQL” in Chapter 1).
  2. Type \d table and then press Enter (Figure 10.6).

    table is a table name. Note that you don’t terminate this command with a semicolon.

    Figure 10.6Displaying a table definition in PostgreSQL.

    Screenshot: A table definition in PostgreSQL

Tips for Table Definitions

Inserting Rows with INSERT

The INSERT statement adds new rows to a table. This section explains how to use several variations of INSERT to:

The important characteristics of INSERT are:

To insert a row by using column positions:

Listing 10.1This INSERT statement adds a new row to the table authors by listing values in the same order in which columns are defined in authors. See Figure 10.7 for the result.

INSERT INTO authors
  VALUES(
    'A08',
    'Michael',
    'Polk',
    '512-953-1231',
    '4028 Guadalupe St',
    'Austin',
    'TX',
    '78701');

To insert a row by using column names:

It’s clearer to list column names in the same order as they appear in the table (Listing 10.2), but you can list them in any order (Listing 10.3). In either case, the values in the VALUES clause must match the sequence in which you list the column names.

Listing 10.2This INSERT statement adds a new row to the table authors by listing column names and values in the same order in which columns are defined in authors. See Figure 10.7 for the result.

INSERT INTO authors(
    au_id,
    au_fname,
    au_lname,
    phone,
    address,
    city,
    state,
    zip)
  VALUES(
    'A09',
    'Irene',
    'Bell',
    '415-225-4689',
    '810 Throckmorton Ave',
    'Mill Valley',
    'CA',
    '94941');

Listing 10.3You don’t have to list column names in the same order in which they’re defined in the table. Here, I’ve rearranged the column names and their corresponding values. See Figure 10.7 for the result.

INSERT INTO authors(
    zip,
    phone,
    address,
    au_lname,
    au_fname,
    state,
    au_id,
    city)
  VALUES(
    '60614',
    '312-998-0020',
    '1937 N. Clark St',
    'Weston',
    'Dianne',
    'IL',
    'A10',
    'Chicago');

You can omit column names if you want to provide values for only some columns explicitly (Listing 10.4). If you omit a column, then the DBMS must be able to provide a value based on the column’s definition. The DBMS will insert the column’s default value (if defined) or null (if allowed). If you omit a column that doesn’t have a default value or allow nulls, then the DBMS will display an error message and won’t insert the row. In this case, the VALUES clause is equivalent to VALUES('A11', 'Max', 'Allard', '212-502-0955', NULL, NULL, NULL, NULL). For information about specifying a default value and allowing nulls, see “Specifying a Default Value with DEFAULT” and “Forbidding Nulls with NOT NULL” in Chapter 11.

Listing 10.4Here, I’ve added a row for a new author but omitted column names and values for the author’s address information. The DBMS inserts nulls into the omitted columns automatically. See Figure 10.7 for the result.

INSERT INTO authors(
    au_id,
    au_fname,
    au_lname,
    phone)
  VALUES(
    'A11',
    'Max',
    'Allard',
    '212-502-0955');

Figure 10.7 shows the new rows in table authors after Listings 10.1 through 10.4 have run.

Figure 10.7The table authors has four new rows after I run Listings 10.1 through 10.4.

au_id au_fname  au_lname    phone        address              city          state zip
----- --------- ----------- ------------ -------------------- ------------- ----- -----
A01   Sarah     Buchman     718-496-7223 75 West 205 St       Bronx         NY    10468
A02   Wendy     Heydemark   303-986-7020 2922 Baseline Rd     Boulder       CO    80303
A03   Hallie    Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco CA    94123
A04   Klee      Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco CA    94123
A05   Christian Kells       212-771-4680 114 Horatio St       New York      NY    10014
A06             Kellsey     650-836-7128 390 Serra Mall       Palo Alto     CA    94305
A07   Paddy     O'Furniture 941-925-0752 1442 Main St         Sarasota      FL    34236
A08   Michael   Polk        512-953-1231 4028 Guadalupe St    Austin        TX    78701
A09   Irene     Bell        415-225-4689 810 Throckmorton Ave Mill Valley   CA    94941
A10   Dianne    Weston      312-998-0020 1937 N. Clark St     Chicago       IL    60614
A11   Max       Allard      212-502-0955 NULL                 NULL          NULL  NULL

To insert rows from one table into another table:

The remaining examples in this section use the table new_publishers (Figure 10.8), which I created to show how INSERT SELECT works. new_publishers has the same structure as the table publishers and acts only as the source of new rows; it isn’t itself changed by the INSERT operations.

Figure 10.8This table, named new_publishers, is used in Listings 10.5 through 10.7. new_publishers has the same structure as publishers.

pub_id pub_name             city        state country
------ -------------------- ----------- ----- --------------
P05    This is Pizza? Press New York    NY    USA
P06    This is Beer? Press  Toronto     ON    Canada
P07    This is Irony? Press London      NULL  United Kingdom
P08    This is Fame? Press  Los Angeles CA    USA

Listing 10.5 inserts the rows for Los Angeles-based publishers from new_publishers into publishers. Here, I’ve omitted the column list, so the DBMS uses the column positions in publishers rather than column names to insert values. This statement inserts one row into publishers; see Figure 10.9 for the result.

Listing 10.5Insert the rows for Los Angeles-based publishers from new_publishers into publishers. See Figure 10.9 for the result.

INSERT INTO publishers
  SELECT
    pub_id,
    pub_name,
    city,
    state,
    country
  FROM new_publishers
  WHERE city = 'Los Angeles';

Listing 10.6 inserts the rows for non-U.S. publishers from new_publishers into publishers. Here, the column names are the same in both the INSERT and SELECT clauses, but they don’t have to match because the DBMS disregards the names of the columns returned by SELECT and uses their positions instead. This statement inserts two rows into publishers; see Figure 10.9 for the result.

Listing 10.6Insert the rows for non-U.S. publishers from new_publishers into publishers. See Figure 10.9 for the result.

INSERT INTO publishers(
    pub_id,
    pub_name,
    city,
    state,
    country)
  SELECT
    pub_id,
    pub_name,
    city,
    state,
    country
  FROM new_publishers
  WHERE country <> 'USA';

It’s legal for the SELECT query to return an empty result (zero rows). Listing 10.7 inserts the rows for publishers named XXX from new_publishers into publishers. I can use SELECT * instead of listing column names because new_publishers and publishers have the same structure. This statement inserts no rows into publishers because no publisher is named XXX; see Figure 10.9 for the result.

Listing 10.7Insert the rows for publishers named XXX from new_publishers into publishers. This statement has no effect on the target table. See Figure 10.9 for the result.

INSERT INTO publishers(
    pub_id,
    pub_name,
    city,
    state,
    country)
  SELECT *
    FROM new_publishers
    WHERE pub_name = 'XXX';

Figure 10.9 shows the table publishers after Listings 10.5 through 10.7 are run.

Figure 10.9The table publishers has three new rows after I run Listings 10.5 through 10.7.

pub_id pub_name             city          state country
------ -------------------- ------------- ----- --------------
P01    Abatis Publishers    New York      NY    USA
P02    Core Dump Books      San Francisco CA    USA
P03    Schadenfreude Press  Hamburg       NULL  Germany
P04    Tenterhooks Press    Berkeley      CA    USA
P06    This is Beer? Press  Toronto       ON    Canada
P07    This is Irony? Press London        NULL  United Kingdom
P08    This is Fame? Press  Los Angeles   CA    USA

Tips for INSERT

Updating Rows with UPDATE

The UPDATE statement changes the values in a table’s existing rows. You can use UPDATE to change:

To update rows, you specify:

The important characteristics of UPDATE are:

To update rows:

Listing 10.8 changes the value of contract to zero in every row of titles. The lack of a WHERE clause tells the DBMS to update all the rows in the column contract. This statement updates 13 rows; see Figure 10.10 for the result.

Listing 10.8Change the value of contract to zero in every row. See Figure 10.10 for the result.

UPDATE titles
  SET contract = 0;

Listing 10.9 uses an arithmetic expression and a WHERE condition to double the price of history books. This statement updates three rows; see Figure 10.10 for the result.

Listing 10.9Double the price of history books. See Figure 10.10 for the result.

UPDATE titles
  SET price = price * 2.0
  WHERE type = 'history';

Here’s a tricky way to change prices with CASE:

UPDATE titles
  SET price = price * CASE type
    WHEN 'history'    THEN 1.10
    WHEN 'psychology' THEN 1.20
    ELSE 1
  END;

Listing 10.10 updates the columns type and pages for psychology books. You use only a single SET clause to update multiple columns, with column = expr expressions separated by commas. (Don’t put a comma after the last expression.) This statement updates three rows; see Figure 10.10 for the result.

Listing 10.10For psychology books, set the type to self help and the number of pages to null. See Figure 10.10 for the result.

UPDATE titles
  SET type = 'self help',
      pages = NULL
  WHERE type = 'psychology';

Listing 10.11 uses a subquery and an aggregate function to cut the sales of books with above-average sales in half. This statement updates two rows; see Figure 10.10 for the result.

Listing 10.11Cut the sales of books with above-average sales in half. See Figure 10.10 for the result.

UPDATE titles
  SET sales = sales * 0.5
  WHERE sales >
    (SELECT AVG(sales)
       FROM titles);

You can update values in a given table based on the values stored in another table. Listing 10.12 uses nested subqueries to update the publication date for all the books written (or cowritten) by Sarah Buchman. This statement updates three rows; see Figure 10.10 for the result.

Listing 10.12Change the publication date of all of Sarah Buchman’s books to January 1, 2003. See Figure 10.10 for the result.

UPDATE titles
  SET pubdate = DATE '2003-01-01'
  WHERE title_id IN
    (SELECT title_id
       FROM title_authors
       WHERE au_id IN
         (SELECT au_id
            FROM authors
            WHERE au_fname = 'Sarah'
              AND au_lname = 'Buchman'));

Suppose that Abatis Publishers (publisher P01) swallows Tenterhooks Press (P04) in a merger, so now, all the Tenterhooks Press books are published by Abatis Publishers. Listing 10.13 works in a bottom-up fashion to change the publisher IDs in titles from P04 to P01. The WHERE subquery retrieves the pub_id for Tenterhooks Press. The DBMS uses this pub_id to retrieve the books in the table titles whose publisher is Tenterhooks Press. Finally, the DBMS uses the value returned by the SET subquery to update the appropriate rows in the table titles. Because the subqueries are used with an unmodified comparison operator, they must be scalar subqueries that return a single value (that is, a one-row, one-column result); see “Comparing a Subquery Value by Using a Comparison Operator” in Chapter 8. Listing 10.13 updates five rows; see Figure 10.10 for the result.

Listing 10.13Change the publisher of all of Tenterhooks Press’s books to Abatis Publishers. See Figure 10.10 for the result.

UPDATE titles
  SET pub_id =
    (SELECT pub_id
       FROM publishers
       WHERE pub_name = 'Abatis Publishers')
  WHERE pub_id =
    (SELECT pub_id
       FROM publishers
       WHERE pub_name = 'Tenterhooks Press');

Figure 10.10 shows the table titles after Listings 10.8 through 10.13 are run. Each listing updates values in a different column (or columns) from those in the other listings. The updated values in each column are highlighted.

Figure 10.10The table titles after I run Listings 10.8 through 10.13. The updated values are highlighted.

title_id title_name                          type      pub_id pages price sales  pubdate    contract
-------- ----------------------------------- --------- ------ ----- ----- ------ ---------- --------
T01      1977!                               history   P01    107   43.98    566 2003-01-01        0
T02      200 Years of German Humor           history   P03     14   39.90   9566 2003-01-01        0
T03      Ask Your System Administrator       computer  P02   1226   39.95  25667 2000-09-01        0
T04      But I Did It Unconsciously          self help P01   NULL   12.99  13001 1999-05-31        0
T05      Exchange of Platitudes              self help P01   NULL    6.95 100720 2001-01-01        0
T06      How About Never?                    biography P01    473   19.95  11320 2000-07-31        0
T07      I Blame My Mother                   biography P03    333   23.95 750100 1999-10-01        0
T08      Just Wait Until After School        children  P01     86   10.00   4095 2001-06-01        0
T09      Kiss My Boo-Boo                     children  P01     22   13.95   5000 2002-05-31        0
T10      Not Without My Faberge Egg          biography P01   NULL    NULL   NULL NULL              0
T11      Perhaps It's a Glandular Problem    self help P01   NULL    7.99  94123 2000-11-30        0
T12      Spontaneous, Not Annoying           biography P01    507   12.99 100001 2000-08-31        0
T13      What Are The Civilian Applications? history   P03    802   59.98  10467 2003-01-01        0

Tips for UPDATE

Deleting Rows with DELETE

The DELETE statement removes rows from a table. You can use DELETE to remove:

To delete rows, you specify:

The important characteristics of DELETE are:

To delete rows:

In the following examples, I’m going to ignore referential-integrity constraints—which I wouldn’t do in a production database, of course.

Listing 10.14 deletes every row in royalties. The lack of a WHERE clause tells the DBMS to delete all the rows. This statement deletes 13 rows; see Figure 10.11 for the result.

Listing 10.14Delete all rows from the table royalties. See Figure 10.11 for the result.

DELETE FROM royalties;

Figure 10.11Result of Listing 10.14.

title_id advance royalty_rate
-------- ------- ------------

The WHERE clause in Listing 10.15 tells the DBMS to remove the authors with the last name Hull from authors. This statement deletes two rows; see Figure 10.12 for the result.

Listing 10.15Delete the rows in which the author’s last name is Hull from the table authors. See Figure 10.12 for the result.

DELETE FROM authors
  WHERE au_lname = 'Hull';

Figure 10.12Result of Listing 10.15.

au_id au_fname  au_lname    phone        address          city       state zip
----- --------- ----------- ------------ ---------------- ---------- ----- -----
A01   Sarah     Buchman     718-496-7223 75 West 205 St   Bronx      NY    10468
A02   Wendy     Heydemark   303-986-7020 2922 Baseline Rd Boulder    CO    80303
A05   Christian Kells       212-771-4680 114 Horatio St   New York   NY    10014
A06             Kellsey     650-836-7128 390 Serra Mall   Palo Alto  CA    94305
A07   Paddy     O'Furniture 941-925-0752 1442 Main St     Sarasota   FL    34236

You can delete rows in a given table based on the values stored in another table. Listing 10.16 uses a subquery to remove all the books published by publishers P01 or P04 from title_authors. This statement deletes 12 rows; see Figure 10.13 for the result.

Listing 10.16Delete the rows for books published by publisher P01 or P04 from the table title_authors. See Figure 10.13 for the result.

DELETE FROM title_authors
  WHERE title_id IN
    (SELECT title_id
       FROM titles
       WHERE pub_id IN ('P01', 'P04'));

Figure 10.13Result of Listing 10.16.

title_id au_id au_order royalty_share
-------- ----- -------- -------------
T02      A01          1          1.00
T03      A05          1          1.00
T07      A02          1          0.50
T07      A04          2          0.50
T13      A01          1          1.00

Tips for DELETE

Truncating Tables

If you want to delete all the rows in a table, then the TRUNCATE statement is faster than DELETE. The SQL:2008 standard introduced TRUNCATE, and Microsoft SQL Server, Oracle, Db2, MySQL, and PostgreSQL support it. TRUNCATE works like a DELETE statement with no WHERE clause: Both remove all rows in a table. But TRUNCATE is faster and uses fewer system resources than DELETE because TRUNCATE doesn’t scan the entire table and record changes in the transaction log (see Chapter 14). The trade-off is that with TRUNCATE, you can’t recover (roll back) your changes if you make a mistake. The syntax is:

TRUNCATE TABLE table;

table is the name of the table to be truncated. For information about TRUNCATE, search your DBMS documentation for truncate.

Older versions of Db2 don’t support TRUNCATE; instead, run LOAD with the REPLACE option, using a zero-byte file as input.