Informix Guide to SQL: Tutorial
Chapter 4: Modifying Data
Home
Contents
Index
Master Index
New Book
Summary
Database access is regulated by the privileges that the database owner grants to you. The privileges that let you query data are often granted automatically, but the ability to modify data is regulated by specific Insert, Delete, and Update privileges that are granted on a table-by-table basis.
If data integrity constraints are imposed on the database, your ability to modify data is restricted by those constraints. Your database- and table-level privileges, along with any data constraints, control how and when you can modify data.
You can delete one or more rows from a table with the
DELETE
statement. Its
WHERE
clause selects the rows; use a
SELECT
statement with the same clause to preview the deletes.
Rows are added to a table with the
INSERT
statement. You can insert a single row that contains specified column values, or you can insert a block of rows that a
SELECT
statement generates.
You use the
UPDATE
statement to modify the contents of existing rows. You specify the new contents with expressions that can include subqueries, so that you can use data that is based on other tables or the updated table itself. The statement has two forms. In the first form, you specify new values column by column. In the second form, a
SELECT
statement or a record variable generates a set of new values.
You use the
REFERENCES
clause of the
CREATE TABLE
and
ALTER TABLE
statements to create relationships between tables. The
ON DELETE CASCADE
option of the
REFERENCES
clause allows you to delete rows from parent and associated child tables with one
DELETE
statement.
You use transactions to prevent unforeseen interruptions in a modification from leaving the database in an indeterminate state. When modifications are performed within a transaction, they are rolled back after an error occurs. The transaction log also extends the periodically made backup copy of the database. If the database must be restored, it can be brought back to its most recent state.
Data replication, which is transparent to users, offers another type of protection from catastrophic failures.
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
