Modifying Data

The following topics cover data modifications:

• Overview of Modifying Data

• Modifying Tables

• Setting Default Values

• Using UPDATE

Overview of Modifying Data

After creating a database, you can modify it as follows:

•After creating tables, you can modify the table definitions.

•After creating columns, you can set optional column attributes.

•After adding data to the database, you can modify the data.

You can perform these tasks using the SQL Data Manager. For information about interactive applications, see PSQL User's Guide. For more information about SQL statements, see SQL Engine Reference.

Modifying Tables

You can use an ALTER TABLE statement to modify a table definition after creating the table. ALTER TABLE statements allow you to add or drop columns; add or drop primary and foreign keys; and change the pathname of a table’s data file.

The following example adds a numeric column called Emergency_Phone to the Tuition table in the sample database.

ALTER TABLE Tuition ADD Emergency_Phone NUMERIC(10,0)#

For more information about columns, refer to Chapter 14, Inserting and Deleting Data. For more information about primary and foreign keys, refer to Chapter 18, Managing Data

Setting Default Values

PSQL inserts a default value if you insert a row but do not provide a value for that column. Default values ensure that each row contains a valid value for the column.

In the Person table of the sample database, all students live in a state. Setting a default value such as TX for the State column ensures that the most probable value is always entered for that column.

To set a default value for a column, use a DEFAULT statement in the CREATE TABLE statement:

CREATE TABLE MyTable(c1 CHAR(3) DEFAULT ’TX’, ID INTEGER)#

SELECT * FROM MyTable#

Result of SELECT Statement:

"c1", "ID"

"TX", "1234"

1 row fetched from 2 columns

Using UPDATE

You can use an UPDATE statement to change the data in a row that is already in a table. UPDATE statements let you modify specific columns in a row. Also, you can use a WHERE clause in an UPDATE statement to specify which rows for PSQL to change. This is referred to as a searched update. Using SQL declared cursors and the Positioned UPDATE statement, you can update the current row of a declared cursor from which you are fetching data.

UPDATE Course
SET Credit_Hours = 4
WHERE Course.Name = 'Math'#

This example instructs PSQL to find the row that contains the course name Math and change the Credit Hours column value to 4.

As shown in the previous example, you can use a constant to update a column by placing it on the right hand side of a SET clause in an UPDATE statement.