SQL Modification Statements

SQL Modification Statements

The SQL Modification Statements make changes to database data in tables and columns. There are 3 modification statements:

• INSERT Statement -- add rows to tables
• UPDATE Statement -- modify columns in table rows
• DELETE Statement -- remove rows from tables

INSERT Statement

The INSERT Statement adds one or more rows to a table. It has two formats:

INSERT INTO table-1 [(column-list)] VALUES (value-list)

and,

INSERT INTO table-1 [(column-list)] (query-specification)

The first form inserts a single row into table-1 and explicitly specifies the column values for the row. The second form uses the result of query-specification to insert one or more rows into table-1. The result rows from the query are the rows added to the insert table. Note: the query cannot reference table-1.

Both forms have an optional column-list specification. Only the columns listed will be assigned values. Unlisted columns are set to null, so unlisted columns must allow nulls. The values from the VALUES Clause (first form) or the columns from the query-specification rows (second form) are assigned to the corresponding column in column-list in order.

If the optional column-list is missing, the default column list is substituted. The default column list contains all columns in table-1 in the order they were declared in CREATE TABLE, or CREATE VIEW.

VALUES Clause

The VALUES Clause in the INSERT Statement provides a set of values to place in the columns of a new row. It has the following general format:

VALUES ( value-1 [, value-2] ... )

value-1 and value-2 are Literal Values or Scalar Expressions involving literals. They can also specify NULL.

The values list in the VALUES clause must match the explicit or implicit column list for INSERT in degree (number of items). They must also match the data type of corresponding column or be convertible to that data type.

INSERT Examples

INSERT INTO p (pno, color) VALUES ('P4', 'Brown')

Before		After
pno descr color P1 Widget Blue P2 Widget Red P3 Dongle Green	=>	pno descr color P1 Widget Blue P2 Widget Red P3 Dongle Green P4 NULL Brown

INSERT INTO sp
SELECT s.sno, p.pno, 500
FROM s, p
WHERE p.color='Green' AND s.city='London'

Before		After
sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 S3 P2 200	=>	sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 S3 P2 200 S2 P3 500

UPDATE Statement

The UPDATE statement modifies columns in selected table rows. It has the following general format:

UPDATE table-1 SET set-list [WHERE predicate]

The optional WHERE Clause has the same format as in the SELECT Statement. See WHERE Clause. The WHERE clause chooses which table rows to update. If it is missing, all rows are in table-1 are updated.

The set-list contains assignments of new values for selected columns. See SET Clause.

The SET Clause expressions and WHERE Clause predicate can contain subqueries, but the subqueries cannot reference table-1. This prevents situations where results are dependent on the order of processing.

SET Clause

The SET Clause in the UPDATE Statement updates (assigns new value to) columns in the selected table rows. It has the following general format:

SET column-1 = value-1 [, column-2 = value-2] ...

column-1 and column-2 are columns in the Update table. value-1 and value-2 are expressions that can reference columns from the update table. They also can be the keyword -- NULL, to set the column to null.

Since the assignment expressions can reference columns from the current row, the expressions are evaluated first. After the values of all Set expressions have been computed, they are then assigned to the referenced columns. This avoids results dependent on the order of processing.

UPDATE Examples

UPDATE sp SET qty = qty + 20

Before		After
sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 S3 P2 200	=>	sno pno qty S1 P1 NULL S2 P1 220 S3 P1 1020 S3 P2 220

UPDATE s
SET name = 'Tony', city = 'Milan'
WHERE sno = 'S3'

Before		After
sno name city S1 Pierre Paris S2 John London S3 Mario Rome	=>	sno name city S1 Pierre Paris S2 John London S3 Tony Milan

DELETE Statement

The DELETE Statement removes selected rows from a table. It has the following general format:

DELETE FROM table-1 [WHERE predicate]

The optional WHERE Clause has the same format as in the SELECT Statement. See WHERE Clause. The WHERE clause chooses which table rows to delete. If it is missing, all rows are in table-1 are removed.

The WHERE Clause predicate can contain subqueries, but the subqueries cannot reference table-1. This prevents situations where results are dependent on the order of processing.

DELETE Examples

DELETE FROM sp WHERE pno = 'P1'

Before		After
sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 S3 P2 200	=>	sno pno qty S3 P2 200

DELETE FROM p WHERE pno NOT IN (SELECT pno FROM sp)

Before		After
pno descr color P1 Widget Blue P2 Widget Red P3 Dongle Green	=>	pno descr color P1 Widget Blue P2 Widget Red