SQL Run

Database Programming

« Previous

Home

Contents

» Next

10. Inserting, Updating, and Deleting Rows

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:

• The INSERT statement adds new rows to a table.
• The UPDATE statement changes the values in a table’s existing rows.
• The DELETE statement removes rows from a table.

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:

• The order of the columns in the table
• Each column’s name
• Each column’s data type
• Whether a column is a key (or part of a key)
• Whether a column’s values must be unique within that column
• Whether a column allows nulls
• Each column’s default value (if any)
• Table and column constraints (Chapter 11)

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:

• Press F11 to show the Navigation pane (on the left), right-click the table name, and then choose Design View in the context menu (Figure 10.1).

  If tables aren’t visible in the Navigation pane, then click the menu at the top of the pane, choose Object Type, click the menu again, and then choose Tables.

  Figure 10.1Displaying a table definition 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.

   

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.

   

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.

   

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.

   

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.

   

Tips for Table Definitions

• To list a table’s column names and the order in which they appear without listing any of the table’s data, type:

  SELECT * FROM table WHERE 1 = 2;

  table is a table name, and 1 = 2 represents any condition that’s always false.

• For general information about columns, see “Tables, Columns, and Rows” in Chapter 2.

  For information about keys, see “Primary Keys” and “Foreign Keys” in Chapter 2.

  For information about data types, see “Data Types” in Chapter 3.

  To modify table definitions, see Chapter 11.

  Table 10.1 shows the commands and queries that list the tables in the current database.

  Table 10.1Listing Database Tables

  DBMS	Command or Query
  Access	Navigation pane (press F11)
  SQL Server	sp_tables
  Oracle	SELECT * FROM TAB;
  Db2	LIST TABLES;
  MySQL	SHOW TABLES;
  PostgreSQL	\d

Inserting Rows with INSERT

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

• Insert a row by using column positions (INSERT VALUES)
• Insert a row by using column names (INSERT VALUES)
• Insert rows from one table into another table (INSERT SELECT)

The important characteristics of INSERT are:

• In a positional insert, you insert ordered values into a new row in the same sequence as the columns appear in a table (see “To insert a row by using column positions” later in this section). In a named-column insert, you name the specific column into which each value is inserted in the new row (see “To insert a row by using column names” later in this section).

  You always should use a named-column insert so your SQL code still will work if someone reorders the table’s columns or adds new columns.

• With INSERT VALUES, you specify explicit values to insert into a table. With INSERT SELECT, you choose rows from another table to insert into a table.
• INSERT VALUES adds one row to a table. INSERT SELECT adds zero or more rows to a table.
• Each inserted value must have the same data type or must be implicitly convertible to the same type as its corresponding column (see “Converting Data Types with CAST()” in Chapter 5).
• To preserve referential integrity, an inserted foreign-key value must contain either null (if allowed) or an existing key value from the primary or unique key column referenced by the foreign key; see “Primary Keys” and “Foreign Keys” in Chapter 2.
• An inserted value can’t violate a check constraint; see “Adding a Check Constraint with CHECK” in Chapter 11.
• No expression can cause an arithmetic error (an overflow or divide-by-zero error, for example).
• Recall from “Tables, Columns, and Rows” in Chapter 2 that the order of rows in a table is unimportant and that you have no control over the physical location of rows, so new rows can appear anywhere among a table’s existing rows.

To insert a row by using column positions:

• Type:

  INSERT INTO table
  VALUES(value1, value2,..., valueN);

  table is the name of a table to insert the row into. value1, value2,..., valueN is a parenthesized list of comma-separated literals or expressions that provides a value to every column in the new row.

  The number of values must equal the number of columns in table, and the values must be listed in the same sequence as the columns in table. The DBMS inserts each value into the column that corresponds to the value’s position in table. value1 is inserted into the first column of table in the new row, value2 into the second column, and so on.

  This statement adds one row to table (Listing 10.1).

To insert a row by using column names:

• Type:

  INSERT INTO table(column1, column2,..., columnN)
  VALUES(value1, value2,..., valueN);

  table is the name of the table to insert the row into. column1, column2,..., columnN is a parenthesized list of comma-separated names of columns in table. value1, value2,..., valueN is a parenthesized list of comma-separated literals or expressions that provides values to the named columns in the new row.

  The number of values must equal the number of columns in the column list, and the values must be listed in the same sequence as the column names. The DBMS inserts each value into a column by using corresponding list positions. value1 is inserted into column1 in the new row, value2 into column2, and so on. An omitted column is assigned its default value or null.

  This statement adds one row to table.

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.

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.

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

To insert rows from one table into another table:

• Type:

  INSERT INTO table[(column1, column2,..., columnN)]
  subquery;

  table is the name of table to insert the rows into. column1, column2,..., columnN is an optional parenthesized list of comma-separated names of columns in table. subquery is a SELECT query that returns rows to insert into table.

  The number of columns in the subquery result must equal the number of columns in table or in the column list. The DBMS ignores the column names in the subquery result and uses column position instead. The first column in the subquery result is used to populate the first column in table or column1, and so on. An omitted column is assigned its default value or null.

  This statement adds zero or more rows to 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.

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.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.

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.

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

Tips for INSERT

• The process of adding rows to a table for the first time is called populating the table.
• If you want to be extra-careful before you insert rows, then you can test your INSERT statement on a temporary copy of the target table; see “Creating a Temporary Table with CREATE TEMPORARY TABLE” and “Creating a New Table from an Existing One with CREATE TABLE AS” in Chapter 11.
• You also can INSERT rows through a view; see “Updating Data Through a View” in Chapter 13.
• If you’re using transactions, then you must use a COMMIT statement after your final INSERT statement to make the changes to the table permanent. For information about transactions, see Chapter 14.

• If table1 and table2 have compatible structures, then you can insert all the rows from table2 into table1 with:

  INSERT INTO table1
  SELECT * FROM table2;

• In some DBMSs, the INTO keyword is optional in an INSERT statement, but you should always include it for portability.

  By default, MySQL (unfortunately) converts some invalid INSERT or UPDATE values and issues a warning instead of triggering an error, which is useless unless you’re using transactions and can roll back the operation. If you insert 9/0, for example, then MySQL will try to insert a null rather than return a division-by-zero error and complain only if the column forbids nulls. If you insert the out-of-range value 999999 into a SMALLINT column, then MySQL will insert 32767 (the largest SMALLINT value) and issue a warning. MySQL provides ERROR_FOR_DIVISION_BY_ZERO, STRICT_ALL_TABLES, STRICT_TRANS_TABLES, and other modes to handle invalid or missing values properly.

  For all DBMSs, check the documentation to see how your DBMS handles the insertion of values into columns whose data type generates a unique row identifier automatically (see “Unique Identifiers” in Chapter 3).

Updating Rows with UPDATE

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

• All rows in a table
• Specific rows in a table

To update rows, you specify:

• The table to update
• The names of the columns to update and their new values
• An optional search condition that specifies which rows to update

The important characteristics of UPDATE are:

• UPDATE takes an optional WHERE clause that specifies which rows to update. Without a WHERE clause, UPDATE changes all the rows in the table.
• UPDATE is dangerous because it’s easy to omit the WHERE clause mistakenly (and update all rows) or misspecify the WHERE search condition (and update the wrong rows). It’s wise to run a SELECT statement that uses the same WHERE clause before running the actual UPDATE statement. Use SELECT * to display all rows that the DBMS will change when you run UPDATE, or use SELECT COUNT(*) to display only the number of rows that will change.
• Each updated value must have the same data type or must be implicitly convertible to the same type as its column; see “Converting Data Types with CAST()” in Chapter 5.
• To preserve referential integrity, you can define the action that the DBMS takes automatically when you try to UPDATE a key value to which foreign-key values point; see “Tips for FOREIGN KEY” in Chapter 11.
• An updated value can’t violate a check constraint; see “Adding a Check Constraint with CHECK” in Chapter 11.
• No expression can cause an arithmetic error (an overflow or divide-by-zero error, for example).
• Recall from “Tables, Columns, and Rows” in Chapter 2 that the order of rows in a table is unimportant and that you have no control over the physical location of rows, so an updated row can change position in a table.

To update rows:

• Type:

  UPDATE table
  SET column = expr
  [WHERE search_condition];

  table is the name of a table to update.

  column is the name of the column in table that contains the rows to change. expr is a literal, an expression, or a parenthesized subquery that returns a single value. The value returned by expr replaces the existing value in column. To change the values in multiple columns, type a list of comma-separated column = expr expressions in the SET clause. You can list the column = expr expressions in any order.

  search_condition specifies the conditions that rows have to meet to be updated. The search_condition conditions can be WHERE conditions (comparison operators, LIKE, BETWEEN, IN, or IS NULL; see Chapter 4) or subquery conditions (comparison operators, IN, ALL, ANY, or EXISTS; see Chapter 8), combined with AND, OR, or NOT. If the WHERE clause is omitted, then every row in table is updated.

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.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.

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.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.

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.

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.

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.

Tips for UPDATE

• A DBMS will evaluate expressions in a SET or WHERE clause by using the values that the referenced columns had before any updates. Consider this UPDATE statement:

  UPDATE mytable
  SET col1 = col1 * 2,
      col2 = col1 * 4,
      col3 = col2 * 8
  WHERE col1 = 1
    AND col2 = 2;

  For the rows matching the WHERE conditions, the DBMS sets col1 to 2, col2 to 4 (1 × 4, not 2 × 4), and col3 to 16 (2 × 8, not 4 × 8).

  This evaluation scheme lets you swap the values of compatible columns with:

  UPDATE mytable
  SET col1 = col2,
      col2 = col1;

  (This trick won’t work in MySQL.)

• If you want to be extra-careful before you update rows, then you can test your UPDATE statement on a temporary copy of the target table; see “Creating a Temporary Table with CREATE TEMPORARY TABLE” and “Creating a New Table from an Existing One with CREATE TABLE AS” in Chapter 11.
• You also can UPDATE rows through a view; see “Updating Data Through a View” in Chapter 13.
• If you’re using transactions, then you must use a COMMIT statement after your final UPDATE statement to make the changes to the table permanent. For information about transactions, see Chapter 14.
• The SQL:2003 standard introduced the MERGE statement as a convenient way to combine multiple UPDATE and INSERT operations in a single statement. These operations informally are called upserts. Oracle and Db2 support MERGE. MySQL has the simplified MERGE variants REPLACE INTO and INSERT...ON DUPLICATE KEY UPDATE.

• In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 10.12, change the date literal to #2003-01-01#.

  Microsoft Access doesn’t support scalar subqueries in the SET clause. To run Listing 10.13, split the UPDATE statement into two statements: one that SELECTs the pub_id for Abatis Publishers from publishers and one that uses this pub_id to change the pub_id of all the Tenterhooks Press books in titles. Then run the statements programmatically (in a host language such as Visual Basic or C#), using the result of the first statement as the input for the second statement.

  In Microsoft SQL Server and Db2 date literals, omit the DATE keyword. To run Listing 10.12, change the date literal to '2003-01-01'.

  MySQL 4.1 and later support subqueries but won’t run Listing 10.11 because MySQL won’t let you use the same table (titles, in this case) for both the subquery’s FROM clause and the update target. Earlier MySQL versions don’t support subqueries and won’t run Listings 10.11, 10.12, and 10.13; for workarounds, see the DBMS tip in “Tips for Subqueries” in Chapter 8.

  For MySQL, see also the DBMS tip in “Tips for INSERT” earlier in this chapter.

  To run Listings 10.9 and 10.11 in older PostgreSQL versions, convert the floating-point numbers to DECIMAL (see “Converting Data Types with CAST()” in Chapter 5). The changes are (Listing 10.9):

  CAST(2.0 AS DECIMAL)

  and (Listing 10.11):

  CAST(0.5 AS DECIMAL)

  For all DBMSs, check the documentation to see how your DBMS handles updating values in columns whose data type generates a unique row identifier automatically (see “Unique Identifiers” in Chapter 3).

Deleting Rows with DELETE

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

• All rows in a table
• Specific rows in a table

To delete rows, you specify:

• The table whose rows to delete
• An optional search condition that specifies which rows to delete

The important characteristics of DELETE are:

• Unlike INSERT and UPDATE, DELETE takes no column names because it removes entire rows.
• DELETE removes rows from a table, but it never deletes the table’s definition. Even if you remove all rows from a table, the table itself still exists. If you want to delete a table definition (and all its associated data, indexes, and so on), then see “Dropping a Table with DROP TABLE” in Chapter 11.
• DELETE takes an optional WHERE clause that specifies which rows to delete. Without a WHERE clause, DELETE removes all the rows in the table.
• DELETE is dangerous because it’s easy to omit the WHERE clause mistakenly (and remove all rows) or misspecify the WHERE search condition (and remove the wrong rows). It’s wise to run a SELECT statement that uses the same WHERE clause before running the actual DELETE statement. Use SELECT * to display all rows that the DBMS will remove when you run DELETE, or use SELECT COUNT(*) to display only the number of rows that will disappear.
• To preserve referential integrity, you can define the action the DBMS takes automatically when you try to DELETE a key value to which foreign-key values point; see “Tips for FOREIGN KEY” in Chapter 11.
• No expression can cause an arithmetic error (an overflow or divide-by-zero error, for example).
• Recall from “Tables, Columns, and Rows” in Chapter 2 that the order of rows in a table is unimportant and that you have no control over the physical location of rows, so a deletion might reorder the positions of the rows remaining in a table.

To delete rows:

• Type:

  DELETE FROM table
  [WHERE search_condition];

  table is the name of a table to delete rows from.

  search_condition specifies the conditions to be met for the rows that are deleted. The search_condition conditions can be WHERE conditions (comparison operators, LIKE, BETWEEN, IN, or IS NULL; see Chapter 4) or subquery conditions (comparison operators, IN, ALL, ANY, or EXISTS; see Chapter 8), combined with AND, OR, or NOT. If the WHERE clause is omitted, then every row in table is deleted.

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.

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.

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.

Tips for DELETE

• If you want to be extra-careful before you remove rows, then you can test your DELETE statement on a temporary copy of the target table; see “Creating a Temporary Table with CREATE TEMPORARY TABLE” and “Creating a New Table from an Existing One with CREATE TABLE AS” in Chapter 11.
• You also can DELETE rows through a view; see “Updating Data Through a View” in Chapter 13.
• If you’re using transactions, then you must use a COMMIT statement after your final DELETE statement to make the changes to the table permanent. For information about transactions, see Chapter 14.

• You can use a NOT EXISTS or NOT IN subquery to delete rows from one table that refer to nonexistent rows in another table (useful for removing orphaned rows or referential-integrity violations). The following statements remove all rows from the table titles for which no publisher exists in the table publishers:

  DELETE FROM titles
  WHERE NOT EXISTS
    (SELECT * FROM publishers
       WHERE publishers.pub_id = titles.pub_id);

  or

  DELETE FROM titles
  WHERE pub_id NOT IN
    (SELECT pub_id FROM publishers);

• In some DBMSs, the FROM keyword is optional in a DELETE statement, but you should always include it for portability.

  MySQL 4.1 and later support subqueries and will run Listing 10.16. Earlier MySQL versions don’t support subqueries and won’t run it; for workarounds, see the DBMS tip in “Tips for Subqueries” in Chapter 8.

  For MySQL, see also the DBMS tip in “Tips for INSERT” earlier in this chapter.