Chapter 5 Updating Data

1. Chapter 5Updating Data

2. 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)

3. 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 <table name> SET <column name >= <new value>

4. Updating a Table

5. Deleting Existing Rows From a Table • To delete data from the database, use the DELETE command • Format for the DELETE command is • DELETE <table name> WHERE <column name> = <value>

6. Deleting a Row

7. 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

8. Creating a Table From an Existing Table

9. Changing a Table’s Structure • To change the structure of an existing table, use the ALTER TABLE command • The format for adding a new column is • ALTER TABLE <table name> ADD <column name> <characteristics>

10. Adding a Column Using ALTER

11. Changing Column Width

12. Changing Columns to NOT NULL or NULL

13. 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 from the original table

14. Dropping a Table • Delete a table using the DROP command • DROP ABLE SALES_REP