Chapter 5 Updating Data

1
Chapter 5Updating Data
2
Objectives

• Use the COMMIT and ROLLBACK commands to make
  permanent data updates or to cancel updates
• Change data using the UPDATE command
• Add new data using the INSERT command
• Delete data using the DELETE command

3
Objectives

• Create a new table from an existing table
• Use nulls in UPDATE commands
• Alter the rows in an existing table
• Change the structure of an existing table

4
COMMIT and ROLLBACK

• Updates to a table
• are temporary and can be cancelled at any time
  during the current work session
• become permanent automatically when the DBMS is
  exited
• Temporary updates can be made permanent
  immediately by running COMMIT command after first
  executing UPDATE command
• Cancel updates with the ROLLBACK command
• Updates since the last COMMIT command will be
  reversed (data only not structure changes)

5
Changing Existing Data in a Table

• Data stored in tables is subject to constant
  change
• Keep data current by using UPDATE to change rows
  on which a specific condition is true
• Format for the UPDATE command is
• UPDATE lttable namegt SET ltcolumn name gt ltnew
  valuegt

6
Example 1

• Change the last name of customer number 256 in
  the Premiere Products database to Jones

7
Updating a Table
8
Using ROLLBACK Command
9
Example 2

• For each customer with a 1,000 credit limit
  whose balance does not exceed the credit limit,
  increase the credit limit to 1,200

10
Using a Compound Condition to Update a Table
11
Increase the Credit Limit by 10

• Use the previous value in a column in the update
• Using the previous example in Figure 5.3, change
  the SET clause to
• SET CREDIT_LIMIT CREDIT_LIMIT 1.10

12
Adding New Rows to An Existing Table

• The INSERT command
• is used to add data to the database
• Is used as a way of updating data in a table

13
Example 3

• Add sales rep number 14 to the SALES_REP table
• Her name is Ann Crane, her address is 123 River
  in Alpen, MI, and her zip code is 49114
• Her commission rate is 5 (0.05), but she has not
  yet earned any commission

14
Inserting a Row
15
Deleting Existing Rows From a Table

• To delete data from the database, use the DELETE
  command
• Format for the DELETE command is
• DELETE lttable namegt WHERE ltcolumn namegt ltvaluegt

16
Example 4

• Delete from the database the customer information
  for Al Williams

17
Deleting a Row
18
Creating a New Table From An Existing Table

• Use the CREATE TABLE command to describe the
  table
• Use the INSERT command to add data to the table
• Use the SELECT command to specify which rows from
  the existing table to insert into the new table

19
Example 5

• Create a new table named SMALL_CUST containing
  the same columns as the CUSTOMER table, and then
  insert into the new table only those rows for
  which the credit limit is 1,200 or less

20
Creating a Table From an Existing Table
21
Results of Table Creation
22
Example 6

• Create a new table named CUST_OF_03, containing
  the customer number, last name, first name, and
  balanced of every customer assigned to sales rep
  number 03

23
Creating a Table That Does Not Contain All the
Columns
24
Inserting Rows
25
Changing a Value in a Column to Null

• In order to make this type of change, the
  affected column must be able to accept nulls
• If specified NOT NULL for the column when it was
  created then changing a value in a column to null
  is prohibited

26
Example 7

• Change the address of customer number 124 in the
  CUSTOMER table to null

27
Setting a Column to Null
28
Changing a Tables Structure

• To change the structure of an existing table, use
  the ALTER TABLE command
• The format for adding a new column is
• ALTER TABLE lttable namegt ADD ltcolumn namegt
  ltcharacteristicsgt

29
Example 8

• Premiere Products decided 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 as a new column in the
  CUSTOMER table

30
Adding a Column Using ALTER
31
Assign Value to Existing Rows

• After altering a table, existing rows must have
  values assigned
• Assign a value of NULL to all existing rows
• Any column added to a table definition must
  accept nulls
• Or
• Allow user to specify an initial value
• ALTER TABLE CUSTOMER
• ADD CUSTOMER_TYPE CHAR(1) INIT R
• Oracle allows only new columns set to null
• Use ALTER TABLE with UPDATE command

32
Making the Same Entry in All Rows
33
Example 9

• Two customers have a type other than R
• The type for customer number 412 should be S, and
  the type for customer number 622 should be D

34
Changing Individual Rows
35
Results of Changes
36
Example 10

• The length of the STREET column is too short
• Increase its length to 20 characters
• Use the MODIFY clause of the ALTER TABLE command

37
Changing Column Width
38
Example 11

• Currently the CITY column can accept nulls
• Change the CITY column so that nulls are not
  allowed
• Currently the FIRST column is not allowed to
  accept nulls
• Change the FIRST column so that nulls are allowed.

39
Changing Columns to NOT NULL or NULL
40
Current Table Structure
41
Making Complex Changes

• Oracle does not allow you to
• reduce the size of a column
• to change a data type
• To make these complex changes
• Use the CREATE TABLE command to describe the new
  table
• insert values into it using the INSERT command
  combined with an appropriate SELECT command

42
Dropping a Table

• Delete a table using the DROP command
• DROP ABLE SALES_REP

43
Summary

• Use the UPDATE command to change existing data in
  a table
• Use the COMMIT command to make updates permanent
  use the ROLLBACK command to cancel any updates
  that have not been committed
• Use the INSERT command to add new rows to a table
• Use the DELETE command to delete existing rows
  from a table

44
Summary

• To create a new table from an existing table,
  first create the new table by using the CREATE
  TABLE command
• Then use an INSERT command containing a SELECT
  clause to select the desired data to be included
  from the existing table
• To change all values in a column to null, the
  clause is SET ltcolumn namegt NULL
• To change a specific value in a column to null,
  use a condition to select the row

45
Summary

• To add a column to a table, use the ALTER TABLE
  command with the ADD clause
• To change the characteristics of a column, use
  the ALTER TABLE command with the MODIFY clause
• Use the DROP TABLE command to delete a table and
  all of its data