DATA MANIPULATION LANGUAGE (DML)

Data manipulation language is the area of SQL that allows you to change data within the database. It consists of only three command statement groups, they are INSERT, UPDATE and DELETE. In this section I will also consider transaction control.

Inserting new rows into a table

We insert new rows into a table with the INSERT INTO command. A simple example is given below.

INSERT INTO JD11.SECTION VALUES (SECIDNUM.NEXTVAL, 'Computing', 0)
The INSERT INTO command is followed by the name of the table (and owning schema if required), this in turn is followed by the VALUES keyword which denotes the start of the value list.

The value list comprises all the values to insert into the specified columns. We have not specified the columns we want to insert into in this example so we must provide a value for each and every column in the correct order.

The correct order of values can be determined by doing a SELECT * or DESCRIBE against the required table, the order that the columns are displayed is the order of the values that you specify in the value list.

If we want to specify columns individually (when not filling all values in a new row) we can do this with a column list specified before the VALUES keyword.

Our example is reworked below, note that we can specify the columns in any order - our values are now in the order that we specified for the column list. 

INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID) VALUES ('Computing',
SECIDNUM.NEXTVAL)
 

In the above example we haven't specified the BOOK_COUNT column so we don't provide a value for it, this column will be set to NULL which is acceptable since we don't have any constraint on the column that would prevent our new row from being inserted. 

The SQL required to generate the data in the two test tables is given below.
INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID)

VALUES ('Fiction', 10);
INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID)
VALUES ('Romance', 5);
INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID)
VALUES ('Science Fiction', 6);
INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID)
VALUES ('Science', 7);
INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID)
VALUES ('Reference', 9);
INSERT INTO JD11.SECTION (SECTION_NAME, SECTION_ID)
VALUES ('Law', 11);


INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (21, 'HELP', 'B.Baker', 20.90, '20-AUG-97', NULL, 10, 9);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (87, 'Killer Bees', 'E.F.Hammond', 29.90, NULL, NULL, NULL, 9);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (90, 'Up the creek', 'K.Klydsy', 15.95, '15-JAN-97', '21-JAN-97', 1,10);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (22, 'Seven seas', 'J.J.Jacobs', 16.00, '21-DEC-97', NULL, 19, 5);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (91, 'Dirty steam trains', 'J.SP.Smith', 8.25, '14-JAN-98', NULL, 98,9);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (101, 'The story of trent', 'T.Wilbury', 17.89, '10-JAN-98','16-JAN-98', 12, 6);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (8, 'Over the past again', 'K.Jenkins', 19.87, NULL, NULL, NULL, 10);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE,
TIMES_LENT, SECTION_ID)
VALUES (79, 'Courses for horses', 'H.Harriot', 10.34, '17-JAN-98', NULL, 12,9);
INSERT INTO JD11.BOOK (ISBN, TITLE, AUTHOR, COST, LENT_DATE, RETURNED_DATE, TIMES_LENT,
SECTION_ID)
VALUES (989, 'Leaning on a tree', 'M.Kilner', 19.41, '12-NOV-97', '22-NOV-97',56, 11);

Copying rows from another table

If we want to copy rows from one table to another we can use a form of the insert command. Obviously the new table must support at least the columns that were present in the old table. This is a good method of updating a table structure, if for instance we wanted to remove the BOOK_COUNT column from the SECTION table (this is a calculated field and need only be derived when required rather than stored in a table) we could create a new table (SECTIONNEW) and populate it with the contents of the old table (SECTION). We could then drop the old table and rename the new table. An
example of the INSERT command that accomplishes this is given below : 

INSERT INTO JD11.SECTIONNEW (SECTION_NAME, SECTION_ID)

SELECT SECTION_NAME, SECTION_ID FROM JD11.SECTION
As you can see we are using a SELECT statement to provide the values for an INSERT command.

Changing row values with UPDATE

The UPDATE command allows you to change the values of rows in a table, you can include a WHERE clause in the same fashion as the SELECT statement to indicate which row(s) you want values changed in. In much the same way as the INSERT statement you specify the columns you want to update and the new values for those specified columns.
The combination of WHERE clause (row selection) and column specification (column selection) allows you to pinpoint exactly the value(s) you want changed.
Unlike the INSERT command the UPDATE command can change multiple rows so you should take care that you are updating only the values you want changed (see the transactions discussion for methods of limiting damage from accidental
updates).

An example is given below, this example will update a single row in our BOOK table :

UPDATE JD11.BOOK SET TITLE = 'Leaning on a wall', AUTHOR = 'J.Killner', TIMES_LENT = 0, LENT_DATE = NULL, RETURNED_DATE = NULL WHERE ISBN = 989

We specify the table to be updated after the UPDATE keyword. Following the SET keyword we specify a comma delimited list of column names / new values, each column to be updated must be specified here (note that you can set columns to NULL by using the NULL keyword instead of a new value).

The WHERE clause follows the last column / new value specification and is constructed in the same way as for the SELECT statement, use the WHERE clause to pinpoint which rows to be updated. If you don't specify a WHERE clause on an UPDATE command all rows will be updated (this may or may not be the desired result).
Using Subqueries in an UPDATE command

Using subqueries within UPDATE commands is similar to using them in SELECT statements.
UPDATE JD11.SECTION SET BOOK_COUNT =  

(SELECT COUNT(*) FROM JD11.BOOK WHERE JD11.BOOK.SECTION_ID = JD11.SECTION.SECTION_ID) WHERE SECTION_ID IN (SELECT SECTION_ID FROM JD11.BOOK)

In this example we have subquery in the WHERE clause to enable us to deal with each row on a case by case basis.
For each section provided from the WHERE clause (identified by the key field
SECTION_ID) we select the count of all book records belonging to that section
and update the BOOK_COUNT field with that value.

Deleting rows with DELETE

The DELETE command allows you to remove rows from a table, you can include a WHERE clause in the same fashion as the SELECT statement to indicate which row(s) you want deleted - in nearly all cases you should specify a WHERE clause, running a DELETE without a WHERE clause deletes ALL rows from the table.
Unlike the INSERT command the DELETE command can change multiple rows so you should take great care that you are deleting only the rows you want removed (see the transactions discussion for methods of limiting damage from accidental deletions).

An example is given below, this example will delete a single row in our BOOK
table :
DELETE FROM JD11.BOOK  WHERE ISBN = 989
The DELETE FROM command is followed by the name of the table from which a row will be deleted, followed by a WHERE clause specifying the column / condition values for the deletion.

DELETE FROM JD11.BOOK WHERE ISBN <> 989

This delete removes all records from the BOOK table except the one specified. Remember that if you omit the WHERE clause all rows will be deleted.
Transactions

A transaction is a unit of work, what the unit is will depend on your application but generally it defines some sequence of statements that accomplish something both useful and atomic. Oracle provides some special SQL statements to help you manage your application work and you can use these to chop your work into small groupings or single items of SQL, these groupings should in themselves represent some logical unit of work.

Transactions are made up of any number of DML commands (commands that update
database data). SELECT statements do not effect the status of a transaction but
DDL commands do. Any DDL command will implicitly save all unsaved updates to
the database so don't mix these command types together as you will find it difficult to exercise any transactional control.

A transaction starts when the first DML or DDL command is executed and stops when a COMMIT / ROLLBACK is issued or a DDL command is issued or the application / machine fails. If the application / machine fails then the last transaction is automatically rolled back.
The COMMIT command

INSERT INTO JD11.SECTIONNEW (SECTION_NAME, SECTION_ID) SELECT
SECTION_NAME, SECTION_ID FROM JD11.SECTION

COMMIT

The COMMIT command saves any unsaved updates to the database and all extant save points and locks are released. You should always issue a COMMIT or ROLLBACK command to finish a unit of work, this will prevent your application leaving the database in an indeterminate state.

The SAVEPOINT command

DELETE FROM JD11.BOOK WHERE ISBN = 989

SAVEPOINT DELETE_FINISHED
INSERT INTO JD11.SECTIONNEW (SECTION_NAME, SECTION_ID) SELECT
SECTION_NAME, SECTION_ID FROM JD11.SECTION

The SAVEPOINT command divides a transaction into smaller sub units. Very large, complex transactions might require dozens or hundreds of DML commands, it may help your application design if you can hold at certain points and ROLLBACK or COMMIT just one part of the total transaction, an example of this is a complex user information input such as entering mathematical modelling data. The SAVEPOINT command has a parameter which is the name of the savepoint.

This name can be used to rollback all updates from the time when you issued the named savepoint. You can reuse the savepoint name, each time you redeclare an existing savepoint the old savepoint of that name is deleted.

The ROLLBACK command

DELETE FROM JD11.BOOK WHERE ISBN = 989

SAVEPOINT DELETE_FINISHED

INSERT INTO JD11.SECTIONNEW (SECTION_NAME, SECTION_ID) SELECT SECTION_NAME,
SECTION_ID FROM JD11.SECTION

ROLLBACK TO DELETE_FINISHED
ROLLBACK
The ROLLBACK command removes pending database updates. Issuing a ROLLBACK without specifying a savepoint name removes all pending updates since the last transaction start event whereas issuing a ROLLBACK with a savepoint name parameter removes all pending updates since the time the specified savepoint was created. The first ROLLBACK above removes all updates associated with the INSERT command. The second ROLLBACK removes all updates associated with the DELETE command.