5.4 Inserting, Updating, and Deleting Data

There are four major statements for working with data in SQL: SELECT, INSERT, DELETE, and UPDATE. We describe the latter three statements in this section. SELECT is covered it in its own section later in this chapter.

5.4.1 Inserting Data

Having created a database and the accompanying tables and indexes, the next step is to insert data into the tables. Inserting a row can follow two different approaches. We show both approaches by inserting the same data for a new customer, Lucy Williams.

Consider an example of the first approach using the customer table:

INSERT INTO customer VALUES (1,'Williams','Lucy','E',3,

'272 Station St','Carlton North','VIC','3054',12,'(613)83008460',

'2002-07-02');

The statement creates a new row in the customer table, then the first value 1 is inserted into the first attribute, cust_id. The second value 'Williams' is inserted into the second attribute surname, 'Lucy' into firstname, and so on.

The number of values inserted is the same as the number of attributes in the table (and an error is generated if the number of values doesn't match the number of attributes). If you don't want to supply data for an attribute, you can include NULL instead of a value (as long as the attribute isn't defined as NOT NULL and NULL is valid for that data type). For example, to create a partial customer row, you could use:

INSERT INTO customer VALUES (1,'Williams','Lucy',NULL,3,

NULL,NULL,NULL,NULL,12,NULL,NULL);

To create an INSERT statement using this first format, you need to know the ordering of the attributes in the table. You can discover the table structure by typing SHOW COLUMNS FROM customer into the MySQL command interpreter or by reviewing the CREATE TABLE statement used to create the table. The SHOW statement is described in detail in Chapter 15.

If you want to insert more than one row, you can write more than one INSERT statement. Alternatively, you can write one INSERT statement and separate each row with a comma. Consider an example that uses the latter approach and inserts the details for two customers:

INSERT INTO customer VALUES (1,'Williams','Lucy','E',3,

'272 Station St','Carlton North','VIC','3054',12,'(613)83008460',

'2002-07-02'), (2,'Williams','Selina','J',4,'12 Hotham St',

'Collingwood','VIC','3066',12,'(613)99255432','1980-06-03');

This approach is the fastest way to insert data into MySQL.

Data can also be inserted using a second approach. Consider this example:

INSERT INTO customer SET cust_id = 1, surname = 'Williams', 

   firstname = 'Lucy', initial='E', title_id=3, 

   address='272 Station St', city='Carlton North',

   state='VIC', zipcode='3054', country_id=12,

   phone='(613)83008460', birth_date='2002-07-10';

In this approach, the attribute name is listed, followed by the assignment operator (=) and then the value to be assigned. This approach doesn't require the same number of values as attributes, and it also allows arbitrary ordering of the attributes. This can save you lots of typing when a row has many attributes but is sparsely populated with values. For example, to create a partial customer row, you could use:

INSERT INTO customer SET cust_id = 653, surname = 'Williams',

   firstname = 'Lucy', title_id = 3, country_id = 12;

The first approach can actually be varied to function in a similar way to the second by including parenthesized attribute names before the VALUES keyword. For example, you can create an incomplete customer row with:

INSERT INTO customer (cust_id, surname, city) 

  VALUES (1, 'Williams','North Carlton');

When inserting data, non-numeric attributes must be enclosed in either single or double quotes. If a string contains single quotation marks, the string can be enclosed in double quotation marks. For example, consider the string "Steve O'Dwyer". Likewise, strings containing double quotation marks can be enclosed in single quotation marks. An alternative approach is to escape the quotation character by using a backslash character; for example, as in the string `Steve O\'Dwyer'. Numeric values can also be enclosed in quotes but they aren't mandatory.

There are other ways to insert data in addition to those discussed here. For example, a popular variation is to insert data from another table using a query or to insert data from a formatted text file. These two approaches and other variants are discussed in Chapter 15.

5.4.1.1 Defaults

If you don't include the value for an attribute, it is set to the DEFAULT value if it's supplied in the table definition or to NULL otherwise (if it is valid for the attribute to be NULL). If an attribute is defined as being NOT NULL and does not have a DEFAULT value, the value that's set depends on the attribute type; for example, integer attributes are set to 0 (which causes an auto_increment attribute to be populated with a new identifier, as discussed next) and strings to the empty string. However, rather than worry about what happens, we recommend that you define a DEFAULT value for any attribute that you don't always want to list in an INSERT statement. Even if you want NULL to be inserted when nothing is provided, you can define it as the DEFAULT.

Inserting NULL into a TIMESTAMP (or any date or time type) attribute stores the current date and time. Inserting 0 into a TIMESTAMP attribute doesn't have the same effect as inserting NULL, because 0 is a valid date and time combination.

5.4.1.2 Auto-increment

MySQL provides a non-standard SQL auto_increment modifier that makes management of primary keys easy; most other database servers provide a similar non-standard feature. The goal of using auto_increment is to make sure that each row in your table has a unique primary key so that you can refer to it in other tables; as discussed previously, this is a common requirement in databases.

The following is a simple table definition that uses the auto_increment feature to create a unique value for the primary key:

CREATE TABLE names (

  id smallint(4) NOT NULL auto_increment,

  name varchar(20),

  PRIMARY KEY (id)

);

You can insert data into this table by setting only the name attribute:

INSERT INTO names SET name = "Bob";

In this example, the id is set to the next available identifier because the default value of an integer attribute is 0 and this invokes the auto_increment feature.

In general, when you insert NULL (or zero) as the next value for an attribute with the auto_increment modifier, the value that is stored is the maximum value + 1. For example, if there are already 10 rows in the names table with id values of 1 to 10, inserting a row with NULL as the id (or not providing an id and invoking the default behavior) creates a row with an id value of 11.

The auto_increment modifier is a useful feature when you want to insert data with a unique primary key, but don't want to have to read the data first to determine the next available value to use. As we show you later in Chapter 8, this also helps avoid concurrency problems (and, therefore, the need for locking) when several users are using the same database. The disadvantage is that it's a proprietary MySQL feature. However, we also show you how to develop a generic approach to managing identifiers in Chapter 9 and we also show you how it's done with PHP's PEAR DB.

Only one attribute in a table can have the auto_increment modifier.

The result of an auto_increment modifier can be checked with the MySQL-specific function last_insert_id( ). For the previous example, you can check which id was created with the statement:

SELECT last_insert_id( );

This statement reports:

+------------------+

| last_insert_id( ) |

+------------------+

|               11 |

+------------------+

1 row in set (0.04 sec)

You can see that the new row has id=11. To check an identifier value, the function should be called immediately after inserting the new row.

5.4.2 Deleting Data

The DELETE statement removes data from tables. For example, the following deletes all data in the customer table but doesn't remove the table:

DELETE FROM customer;

A DELETE statement with a WHERE clause can remove specific rows; WHERE clauses are frequently used in querying, and they are explained later in Section 5.5. Consider a simple example:

DELETE FROM customer WHERE cust_id = 1;

This deletes the customer with a cust_id value of 1. Consider another example:

DELETE FROM customer WHERE surname = 'Smith';

This removes all rows for customers with a surname value of Smith.

5.4.3 Updating Data

Data can be updated using a similar syntax to the INSERT statement. Consider an example:

UPDATE customer SET state = upper(state);

This replaces the string values of all state attributes with the same string in uppercase. The function upper( ) is one of many MySQL functions discussed in Chapter 15.

You can update more than one attribute in a statement. For example, to set both the state and city to uppercase, use:

UPDATE customer SET state = upper(state), city = upper(city);

The UPDATE statement is also often used with the WHERE clause. For example:

UPDATE customer SET surname = 'Smith' WHERE cust_id = 7;

This updates the surname attribute of customer #7. Consider a second example:

UPDATE customer SET zipcode = '3001' WHERE city = 'Melbourne';

This updates the zipcode of all rows with a city value Melbourne.

After an UPDATE is completed, MySQL returns the number of rows that were changed. If MySQL finds that a value doesn't need to be changed (because it's already set to the value you want to change it to), it isn't updated and isn't included in the count that's returned.