Transcript Chapter 5 Updating Data

Chapter 5
Updating Data
A Guide to SQL, Sixth Edition
1
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
2
Objectives
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
3
Creating a New Table from an
Existing Table
It is possible to create a new table from data
in an existing table
Problem:

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.
4
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) );
5
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
6
Creating a New Table from an
Existing Table
Problem:

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.
7
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;
8
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:


UPDATE <table name> SET <column name> =
<new value>
A WHERE clause can be included to indicate the
row(s) on which the change is to take place
9
Adding New Rows to an
Existing Table
The INSERT command can be used to:
Add data to the tables
 Update table data

Problem:

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.
10
Adding New Rows to an
Existing Table
Solution:

INSERT INTO LEVEL1_CUSTOMER
VALUES
('895','Peter and Margaret''s', 0, 8000, '20');
11
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
12
Commit and Rollback
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
13
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
14
Transactions
The COMMIT and ROLLBACK commands
are used with transactions as follows:



Before beginning the updates for a transaction,
execute the COMMIT command
Complete the updates for the transaction. If any
update cannot be completed, execute the
ROLLBACK command and discontinue the
updates for the current transaction
Execute the COMMIT command after completing
the final update
15
Deleting Existing Rows from a
Table
The DELETE command is used to
delete data from the database
The format for the DELETE command
is:

DELETE <table name> WHERE <column
name> = <value>
16
Deleting Existing Rows from a
Table
Problem:

In the LEVEL1_CUSTOMER table, change
the name of customer 356 to Smith Sport,
and then delete customer 895.
17
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;
18
Executing a Rollback
Problem:

Execute a rollback and then display the
data in the LEVEL1_CUSTOMER table
Solution:
ROLLBACK;
SELECT *
FROM LEVEL1_CUSTOMER;
19
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
20
Changing a Value in a Column
to Null
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
21
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:

ALTER TABLE <table name> ADD
<column name> <characteristics>
22
Changing Table Structures
Problem:

Premiere Products decides to maintain a
customer type for each customer in the
database. These types are R for regular
customers, D for distributors, and S for
special customers. Add this information in
a new column in the LEVEL1_CUSTOMER
table.
23
Changing Table Structures
Solution:
ALTER TABLE LEVEL1_CUSTOMER
ADD CUSTOMER_TYPE CHAR(1);
24
Changing Table Structures
The characteristics of existing columns can
be changed by using the MODIFY clause of
the ALTER TABLE command
Problem:

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.
25
Changing Table Structures
Solution:
ALTER TABLE LEVEL1_CUSTOMER
MODIFY CUSTOMER_NAME CHAR(50);
ALTER TABLE LEVEL1_CUSTOMER
MODIFY CREDIT_LIMIT NOT NULL;
26
Making Complex Changes
Some changes to a table’s structure are
beyond the capabilities of some DBMSs
Examples include:
Eliminate a column
 Change the column order
 Combine data from two tables into one

27
Making Complex Changes
To make complex changes:
Use the CREATE TABLE command to
describe the new table
 Insert values into the new table using the
INSERT command combined with an
appropriate SELECT command

28
Dropping a Table
A table that is no longer needed can be
deleted by using the DROP TABLE command
Problem:

Delete the LEVEL1_CUSTOMER table because it
is no longer needed in the Premiere Products
database.
Solution:
DROP TABLE LEVEL1_CUSTOMER;
29
Summary
To create a new table from an existing table:


Create the new table by using the CREATE
TABLE command
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
30
Summary
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
31
SQL Project Five Completed
Good Luck
H. Zamanzadeh
32