Database Tutorial

Lab #6: SQL UPDATE Statement

Learn how to use ALTER TABLE and UPDATE statements to modify an existing table with data already inserted into the table.

• Lab
  • Description
  • Help Section
  • Instructions

Objectives

The lab is designed to teach you how to alter table structures, insert related data across two or more tables, and how to update data in a table. Specifically, you should learn the following:

• Learn how to use the ALTER statement to add two new columns and add a foreign key constraint to an existing table.
• Learn how to use the CREATE statement to create a new table with foreign key constraints.
• Learn how to use the ALTER statement to modify a column’s name.
• Learn how to couple several INSERT statements together into a transaction.
• Learn how to use the UPDATE statement to change data.

Business Scenario

Business users often find errors in application software after it has been developed and deployed. Those errors become maintenance programming tasks for application designers and developers. Many of these types of errors require refactoring existing database tables.

Changes in how database tables are organized after the entry of data becomes much more complex. You often need to add columns and migrate data. Some of the changes requiring adding rows to tables, which changes primary key values. Such changes in primary key values requires updating all foreign keys that referenced the older primary key values.

This lab has you add columns and rows to the COMMON_LOOKUP table. After making the changes to the COMMON_LOOKUP table, you need to change the foreign keys in any tables that reference the old rows.

Overview

The lab is designed to teach you how to alter the structure of an existing table, how to insert into data into a series of tables, and how to update existing data in a table.

ALTER Statement

• Add new columns to an existing table.
• Add new column constraints to an existing table.
• Modify columns in an existing table, like changing their data types, null or not null properties, and physical size.
• Modify column constraints in an existing table.
• Drop a column from an existing table.
• Remove a constraint from a table.

CREATE Statement

• Create a new table with columns and constraints.
• Create a new sequence.

INSERT Statement

• Insert a new row into a table with a VALUES clause or subquery.
• Sequencing INSERT statements lets you manage primary and foreign key assignments with the .NEXTVAL and .CURRVAL pseudo columns.
• Non-sequencing INSERT statements compels you to use subqueries to lookup a primary key value with a SELECT statement, and lets you use the returned value in the scope of the INSERT statement.

Transaction Control

Transaction Control Language (TCL) lets you build robust application software by extending ACID-compliant control across multiple INSERT, UPDATE, and DELETE statements.

• A SAVEPOINT lets you mark a starting point, like a bookmark in a book.
• A ROLLBACK lets you undo work to the beginning of a session, the last COMMIT statement, or to a SAVEPOINT.
• A COMMIT lets you make adds, deletes, and changes permanent.