CIS 331: Database Management Using SQL

A Guide to MySQL, Chapter 6: Updating Data

Objectives:
 

This chapter introduces the student to commands associated with updating files. The objectives important to this chapter are:

  1. Creating a new table based on an existing table
  2. Using UPDATE
  3. Using INSERT
  4. Using COMMIT to save changes, and ROLLBACK prevent saving changes
  5. Supporting transactions with COMMIT and ROLLBACK
  6. Removing rows with DELETE
  7. UPDATE commands with NULLs
  8. Changing table structures
  9. Dropping tables
Concepts:

The text explains a two step method for creating a new table with data from one you already have. In step 1, you create a table with a structure description that matches your existing table:

CREATE TABLE new_table_name
(field_name data_type(width),
field_name data_type(width),
field_name data_type(width) ) ;

In step 2, you use the INSERT command to copy records from the original table to the new table. A WHERE clause is used to restrict which records are copied. Format:

INSERT INTO new_table_name
SELECT column list
FROM original_table
WHERE criteria_to_match ;

Leaving out the WHERE clause would copy all records from the original table to the new table.

The UPDATE command is used to make changes in existing tables. Format:

UPDATE table_name
SET column_name = new_value
WHERE criteria_to_match ;

  • UPDATE is followed by the name of the table being updated.
  • SET is followed by an expression naming the field to change, and the new value to store in that field. The text explains that you can calculate a new value based on the existing value. For example, you could enter:
    SET column_name = column_name * 2
    This would place a value that was twice the original value in the field. This works because the system evaluates the phrase on the right of an equal sign before it carries out the new value assignment. Make sure you understand this: this clause does not change the name of the column. It manipulates and changes data stored in that column.
  • WHERE is followed by criteria identifying which records to make the change in. Compound criteria are allowed.

The INSERT command is used to add new records to a table. You have already used this command to populate your tables. Format:

INSERT INTO table_name
VALUES
('value', 'value', 'value', 'value', numeric_value, 'value' ) ;

  • INSERT INTO is followed by the name of the table
  • VALUES is followed by a comma separated list of values for each field in a record. The list is enclosed in parentheses. Note that numeric values do not have to be enclosed in quotation marks on most systems.

The text explains that every change you make to data in a file is not actually placed in your data file until the change is saved. This is like working on a word processing document for an hour, and then saving a copy of it. Until you save, the changes are only in your computer's RAM, and they could be lost to a power failure.

One way to save the changes is to close the DBMS. Another way is to use the COMMIT command. This is a simple command, in that it takes no arguments. Conversely, to erase the changes made since the last save, you can use the ROLLBACK command. Like COMMIT, ROLLBACK does not take any arguments. This command restores data back to the state before the changes were made. An exception to the use of ROLLBACK is that you cannot use it to undo structural changes to a table, only data changes.

A DBMS may use COMMIT and ROLLBACK to implement a transaction system. The text defines a transaction as a set of steps necessary to accomplish a task. This is flexible enough to allow a transaction to be any number of steps, and a task to be anything you can do with a database. When building a system to support transactions, you should build in three parts:

  1. Execute a COMMIT command before beginning a transaction. This will set the point to which you can roll back.
  2. At the end of a successful transaction, execute a COMMIT command. This provides you with incremental saves.
  3. If a transaction cannot be completed, execute a ROLLBACK command. This will undo the changes since the last COMMIT.

Rows can be removed from a table with the DELETE command. The DELETE command should be used with a WHERE clause to limit which rows to delete. Without the WHERE clause, the command would delete all rows from the table. Format:

DELETE FROM table_name
WHERE criteria ;

A specific use of the UPDATE command is to place nulls into fields. A null is not a value, it is the absence of a value. Placing a null into a field takes all value out of the field. A null is represented by the word NULL. Quotes are not used with NULL. Format:

UPDATE table_name
SET column_name = NULL
WHERE criteria_to_match ;

To change the structure of a table, you can use the ALTER TABLE command. The text gives the example of adding a new column to a table. Format:

ALTER TABLE table_name
ADD new_column data_type(width) ;

The syntax allows you to set a value for all rows in the table, if desired. If some rows should have different values, you can use UPDATE commands to change those rows.

ALTER TABLE table_name
ADD new_column data_type(width) INIT = 'value' ;

Another variation is to change the type or width of a column with the MODIFY clause:

ALTER TABLE table_name
MODIFY column_name data_type(new_width) ;

MODIFY can also be used to change a column to not accept nulls. The student should be aware that some systems will allow you to increase, but not decrease the width of a column. Some systems will not allow changes in data types. If your system does not allow changes to tables, you can still create a new table, and copy data with the INSERT INTO command shown above.

As noted in another chapter, you can remove a table from your database with the DROP TABLE command. The syntax is simple:

DROP TABLE table_name ;