Oracle Certification - SQL Fundamentals I - DML

Data Manipulation Language (DML)

There are 5 DML commands (although generally speaking most people would consider SELECT to be separate):.

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE

TRUNCATE is actually a DDL (Data Definition Language) statement, however because it has a similar affect to a DELETE statement it is generally included in this group.

INSERT

Database constraints determine what data can be inserted into a table, and any INSERT statement must abide by these rules. An INSERT statement can either specify column values in the command in which case it will insert a single row, or a set of rows in which case the rows to be created are specified with a SELECT statement.

Note: There are more efficient ways to insert data into tables, namely SQL*Loader and Oracle Data Pump.

INSERT INTO table [(column [,column...])] VALUES (value [,value...])

An example fully defining the schema, table, columns and values to insert:

Here the columns to insert into are implied, and the values must appear in the exact order in which they are present in the table. This statement would not be good form, as for example if the database where to be recreated the order of the columns could change:

INSERT INTO table [ (column [, column...] ) ] subquery

An INSERT statement used in combination with a subquery is a powerful method of creating new rows using existing data:

UPDATE

An UPDATE statement allows existing data with a table to be changed. The statement can affect a single row or multiple rows, dependant on the WHERE clause which follows the same syntax as any SELECT statement. The UPDATE statement also specifies which columns in the table to update, which can be all the columns of a subset of them. As with any DML statement the constraints in place for the table being altered must be honoured.

Note: An UPDATE statement can only ever change the columns in a single table.

UPDATE table SET column=value [,column=value...] [WHERE condition]

A simple UPDATE statement:

A more complex script to split region 'Americas' into North and South America

UPDATE table
SET column=[subquery] [,column=subquery...]
WHERE column = (subquery) [AND column=subquery...]

The following script recombines the countries in North and South America, and puts them back into the region Americas, removing the new region as well. Note the use of subqueries in the second update statement:

DELETE

The DELETE statement is used to remove rows from a table. The rows that will be removed is determined by the WHERE clause, and if no WHERE clause is specified all the rows in the table will be removed.

Note: DELETE removes an entire row, not portions of it.

DELETE FROM table [WHERE condition]

There's not much to elaborate on with the DELETE statement, suffice to say that rows are deleted based on any rows that satisfy the WHERE clause:

Or when no WHERE clause is present all rows are deleted:

MERGE

MERGE (sometimes called UPSERT) was introduced with Oracle 9i. MERGE can perform an UPDATE, INSERT or DELETE based on a set of rules.

TRUNCATE

TRUNCATE is a DDL (Data Definition Language) statement. This distinction from INSERT, UPDATE, DELETE and MERGE is very important as any DDL statements cannot be rolled back after their execution. There is also no WHERE clause in a TRUNCATE command, the entire table is emptied of it's contents. The main advantage of a TRUNCATE statement over a DELETE statement is in performance. With a TRUNCATE statement the entire contents of a table can be removed without the additional overheads of a transaction.

Note: The affects of a TRUNCATE command are immediate and permanent. Unlike a DELETE statement the transaction does not need committing, and cannot be rolled back.

TRUNCATE TABLE table

There will certainly be some questions on DML / DDL statements in the OCP exams, so make sure you understand them all.

The next topic is Transactions.