SQL INSERT, UPDATE, and DELETE Commands

The SQL INSERT, UPDATE, and DELETE commands enable SQL users to manipulate and modify data:

• The INSERT statement introduces new rows into an existing table.
• The DELETE statement removes a row or combination of rows from a table.
• The UPDATE statement enables users to update a row or group of rows in a table.

You can issue an SQL INSERT, UPDATE, or DELETE command against one segment instance (row) at a time. When you issue one of these commands against a multi-segment Master File:

• All fields referenced in the command must be on a single path through the file structure.
• The command must explicitly specify (in the WHERE predicate) every key value from the root to the target segment instance, and this combination of key values must uniquely identify one segment instance (row) to be affected by the command.

  If you are modifying every field in the row, you can omit the list of field names from the command.

• The SQL Translator supports subqueries, such as:

  INSERT...INTO...SELECT...FROM...

Although each INSERT, UPDATE, or DELETE command can specify only one row, referential integrity constraints may produce the following modifications to the data source:

• If you delete a segment instance that has descendant segment instances (children), the children are automatically deleted.
• If you insert a segment for which parent segments are missing, the parent segments are automatically created.