Data Manipulation Commands

In this section: INSERT DELETE UPDATE

The server provides three basic commands for manipulating or modifying data:

• The INSERT statement introduces new rows into an existing table. Insertion can be accomplished one row at a time (insert-row) or in a block (insert-set).
• The DELETE statement removes a row or combination of rows from a table.
• The UPDATE statement permits the user to update a row or group of rows in a table.

When a WHERE clause is used in conjunction with an INSERT, DELETE, or UPDATE, it describes what is known as an SQL searched statement.

• In a searched update, every row that meets the search criteria is updated.
• In a searched delete, every row satisfying the condition specified in the WHERE clause is removed from the specified table.
• In a searched insert, a row is inserted in the column that satisfies the condition specified in the WHERE clause.

INSERT

Inserts rows or values into an existing table. The insertion of rows can be accomplished one row at a time (insert-row) or in a block (insert-set). You can insert rows or values into a table with a VALUE keyword or with a SELECT statement.

insert-statement ::= <insert-row> | <insert-set>
insert-set       ::= <insert-head> <query-spec>
insert-row       ::= <insert-head> [(<column-list>)] VALUES
                     (<val-list>)
insert-head      ::= INSERT INTO <table-name>
val-list         ::= <literal> [, <literal>]*

Syntax: How to INSERT Values Into the Employee Table

INSERT INTO <table-name> VALUES (literal1 [, literal2]
[, literal3]....)

where:

table-name

Is the table name.

literal

Is a non-null value or constant: a specific unchangeable value that satisfies the constraints of its data type.

Example: Inserting Values Into the Employee Table

INSERT INTO EMPLOYEE VALUES
'12345678','Joseph','Bloggs','123 Main Street','New York',
'NY','G','M',  '212 321-4561'

DELETE

Removes a row or a combination of rows, based on the search criteria specified.

delete-statement: searched ::= DELETE FROM <table-name> [Where 
<search condition>]

where:

table-name

Is the name of the table that contains the data you want to delete.

Where

Specifies the search criteria that must be met in order for the deletion to take place.

If a search condition is not specified, then all rows of the table are marked for deletion. If a search condition is specified, only those rows that meet the search criteria are marked for deletion.

Example: Deleting Rows in the Employee Table

The following statement deletes all rows in the Employee table whose company name is Ragmount.

DELETE FROM EMPLOYEE WHERE company = 'Ragmount'

UPDATE

Changes the contents of a row or a group of rows in a table.

update-statement ::= UPDATE <table-name>
                     SET <asg-list> [<where-clause>]
asg-list         ::= <asg-element> [, <asg-element>]*
asg-element      ::= <column-name> = {<scalar-expression> | NULL }

A WHERE clause is typically used with the UPDATE command to define which rows should be modified. Subqueries can also be included in the WHERE.

Example: Changing the Parts Row

UPDATE PARTS
SET PRICE = 1.1 * PRICE
WHERE Partnum='R467Z'