Chapter 5
Updating Data

Objectives

Create a new table from an existing table

Change data using the UPDATE command

Add new data using the INSERT command

Use the COMMIT and ROLLBACK commands to make permanent data updates or to reverse updates

Understand transactions and the role of COMMIT and ROLLBACK in supporting transactions

Delete data using the DELETE command

Use nulls in UPDATE commands

Change the structure of an existing table

Drop a table

Creating a New Table from an Existing Table

It is possible to create a new table from data in an existing table

Problem:

l Create a new table named LEVEL1_CUSTOMER containing the following columns from the CUSTOMER table: CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, and REP_NUM. The columns in the new LEVEL1_CUSTOMER table should have the same characteristics as the corresponding columns in the CUSTOMER table.

Creating a New Table from an Existing Table

Solution:

CREATE TABLE LEVEL1_CUSTOMER
(CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CUSTOMER_NAME CHAR(35),
BALANCE DECIMAL(8,2),
CREDIT_LIMIT DECIMAL(8,2),
REP_NUM CHAR(2) );

Creating a New Table from an Existing Table

A new table can be described with the CREATE TABLE command

A SELECT command selects data from the CUSTOMER table

By placing this SELECT command in an INSERT command, the query results are added to the new table

Creating a New Table from an Existing Table

Problem:

l Insert into the LEVEL1_CUSTOMER table the customer number, customer name, balance, credit limit, and rep number for customers with credit limits of $7,500.

Creating a New Table from an Existing Table

Solution:

INSERT INTO LEVEL1_CUSTOMER
SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, REP_NUM
FROM CUSTOMER
WHERE CREDIT_LIMIT = 7500;

Changing Existing Data in a Table

The data stored in a database is subject to constant change

The UPDATE command is used to change rows on which a specific condition is true

Format for the UPDATE command:

l UPDATE <table name> SET <column name> = <new value>

l A WHERE clause can be included to indicate the row(s) on which the change is to take place

Adding New Rows to an Existing Table

The INSERT command can be used to:

l Add data to the tables

l Update table data

Problem:

l Add customer number 895 to the LEVEL1_CUSTOMER table. The name is Peter and Margaret’s, the balance is 0, the credit limit is $8,000, and the rep number is 20.

Adding New Rows to an Existing Table

Solution:

l INSERT INTO LEVEL1_CUSTOMER
VALUES
('895','Peter and Margaret''s', 0, 8000, '20');

Commit and Rollback

Updates to data are only temporary

Updates can be reversed (cancelled) at any time during the current work session

Updates become permanent automatically when the DBMS is exited

Updates can be saved immediately by executing the COMMIT command

Updates can be cancelled by executing the ROLLBACK command

Updates since the last COMMIT command will be reversed

The ROLLBACK command reverses only changes made to the data, not the table’s structure

Transactions

A transaction is a logical unit of work

A transaction can be viewed as a sequence of steps that accomplishes a single task

It is essential that the entire sequence is completed successfully

A transaction is a logical unit of work

A transaction can be viewed as a sequence of steps that accomplishes a single task

It is essential that the entire sequence is completed successfully

Deleting Existing Rows from a Table

The DELETE command is used to delete data from the database

The format for the DELETE command is:

l DELETE <table name> WHERE <column name> = <value>

Deleting Existing Rows from a Table

Problem:

l In the LEVEL1_CUSTOMER table, change the name of customer 356 to Smith Sport, and then delete customer 895.

Deleting Existing Rows from a Table

Solution:

UPDATE LEVEL1_CUSTOMER
SET CUSTOMER_NAME = 'Smith Sport‘
WHERE CUSTOMER_NUM = '356';

DELETE FROM LEVEL1_CUSTOMER
WHERE CUSTOMER_NUM = '895';

SELECT *
FROM LEVEL1_CUSTOMER;

Executing a Rollback

Problem:

l Execute a rollback and then display the data in the LEVEL1_CUSTOMER table

Solution:

ROLLBACK;
SELECT *
FROM LEVEL1_CUSTOMER;

Changing a Value in a Column to Null

The value in a column in an existing row can be changed to null

To make this type of change, the affected column must accept nulls

If NOT NULL was specified for the column when it was created, then changing a value to null is prohibited

The command for changing the value to null is the same as it would be for changing any other value

The value NULL is used as the replacement value

Changing Table Structures

A table’s structure can be changed by using the ALTER TABLE command

To add a new column, the ADD clause of the ALTER TABLE command is used

The format for adding a new column is:

l ALTER TABLE <table name> ADD <column name> <characteristics>

Changing Table Structures

Solution:

ALTER TABLE LEVEL1_CUSTOMER
ADD CUSTOMER_TYPE CHAR(1);

Changing Table Structures

The characteristics of existing columns can be changed by using the MODIFY clause of the ALTER TABLE command

Problem:

l The length of the CUSTOMER_NAME column in the LEVEL1_CUSTOMER table is too short. Increase its length to 50 characters. In addition, change the CREDIT_LIMIT column so that it cannot accept nulls.

Changing Table Structures

Solution:

ALTER TABLE LEVEL1_CUSTOMER
MODIFY CUSTOMER_NAME CHAR(50);

ALTER TABLE LEVEL1_CUSTOMER
MODIFY CREDIT_LIMIT NOT NULL;

Making Complex Changes

Some changes to a table’s structure are beyond the capabilities of some DBMSs

Examples include:

l Eliminate a column

l Change the column order

l Combine data from two tables into one

To make complex changes:

l Use the CREATE TABLE command to describe the new table

l Insert values into the new table using the INSERT command combined with an appropriate SELECT command

Dropping a Table

A table that is no longer needed can be deleted by using the DROP TABLE command

Problem:

l Delete the LEVEL1_CUSTOMER table because it is no longer needed in the Premiere Products database.

Solution:

DROP TABLE LEVEL1_CUSTOMER;

Summary

To create a new table from an existing table:

l Create the new table by using the CREATE TABLE command

l Use an INSERT command containing a SELECT command to select the desired data from the existing table

UPDATE command: changes existing data in a table

INSERT command: adds new rows to a table

COMMIT command: saves updates

ROLLBACK command: reverses updates

DELETE command: deletes existing rows

To add a column to a table, use the ALTER TABLE command with an ADD clause

To change the characteristics of a column, use the ALTER TABLE command with a MODIFY clause

SQL Project Five Completed

Good Luck

H. Zamanzadeh