Oracle DML Statements

This SQL Tutorial focuses on the Oracle DML statements. DML (Data Manipulation Language) statements are the element in the SQL language that is used for data retrieval and manipulation. Using these statements you can perform operations such as: adding new rows, updating and deleting existing rows, merging tables and so on.

This SQL tutorial allows you to become familiar with the following topics :

• INSERT – adding new rows to a table
  • Basic Syntax
  • Using a Column List
  • INSERT INTO SELECT
• UPDATE – updating values of fields
• DELETE – deleting rows from a table
• MERGE – merging tables
• TRANSACTIONS – learn what transactions are and how they can be managed

Please note – the SQL SELECT statement, another DML component, is not covered in this tutorial. For more details about the SQL SELECT statement please use the following link.

Oracle DML INSERT Statement

The Oracle INSERT statement is used to add new rows to a table. Before you become familiar with this statement, please take into account the following points:

• Default value – when creating a new table (DDL), it is possible to define a specific column with a default value. This means that unless otherwise indicated, this is the value that you would like to enter into the field of that column. For example: default price is 50, and default expiration date is ’01-JAN-2020′.
• The following demonstrations are based on the table below, where the default value 57 is assigned to the product_price column and currently the table has a single row.

Basic Syntax

INSERT INTO table_name
VALUES (value, value, value  …)

Inserting a new row into the table

INSERT INTO products
VALUES (2 , 'Coffee' , 3200 , '19-MAY-2016')

The following table reflects the data in products table after the INSERT statement has completed:

Important Points

• The order of the values must match the order of the columns in the table. In the case above, the first value must be associated with the product number column, the second value must be associated with the product name column, and so on.
• The value type must match the type of columns in the table. For example, it is not possible to enter the value 37 into the expiration_date column.
• The number of values – must match the number of values in the table. It is not possible to enter more or less values than the number of values in the table (in this case, 4 values).
• To enter a NULL value into one of the fields of one of the columns, the NULL keyword should be used.

INSERT INTO products
VALUES (3 , NULL , 32 , '09-SEP-2009')

• To enter a DEFAULT value into one of the fields of one of the columns, the DEFAULT keyword should be used.

INSERT INTO products
VALUES (4 , 'Soda', DEFAULT , '09-SEP-2011')

• SYSDATE – you can use the SYSDATE function instead of writing a date.

INSERT INTO products
VALUES (4 , 'Pasta', DEFAULT , SYSDATE)

Using a Column List

INSERT INTO  table_name (column_name , column_name , column_name ...)
VALUES (value, value, value …)

The following example uses a column list to explicitly specify the values that are inserted into each column. This option provides us with additional flexibility:

INSERT INTO products (product_id , product_name , product_price ,  expiration_date)
VALUES (5 , 'Sugar' , 3200 , SYSDATE)

INSERT INTO products (product_id , product_name)
VALUES (6 , 'Yogurt')

A NULL value is entered into a column that was not specified in the column list, if no default value exists; otherwise, the DEFAULT value is entered.

• To enter a NULL value explicitly into one of the fields of one of the columns, the NULL value should be specified.

INSERT INTO products (product_id , product_name, product_price)
VALUES (6 , 'Milk' , NULL)

• To enter a DEFAULT value explicitly into one of the fields of one of the columns, the DEFAULT value should be specified.

INSERT INTO products (product_id , product_name, product_price)
VALUES (7, 'ketchup' , DEFAULT)

• GETDATE – you can use the SYSDATE (SYSDATE in Oracle) function instead of writing a date.

Oracle INSERT INTO SELECT

This method allows copying data items from another table into the requested target table.

INSERT INTO  target_table_name (column_name, column_name, column_name ..)
SELECT  …
FROM    source_table_name
WHERE   …

For example:

INSERT INTO products (product_id , product_name , product_price)
SELECT product_id , product_name , price
FROM   different_products_table
WHERE  category_id = 50

• You can use the INSERT INTO SELECT statement either with or without the column list
• It is not mandatory to include a WHERE statement in the query; however, including such a statement is advisable if you would like to avoid copying the entire table.

Oracle DML UPDATE Statement

The Oracle UPDATE statement is used to modify existing rows.

UPDATE table_name
SET column=value, column=value ..
WHERE condition

Updating the price of product no. 100:

UPDATE products
SET price = 5000
WHERE product_id = 100

It is possible to update several fields at the same time, for example: modify the price and the name of product no. 100

UPDATE products
SET price = 5000 , product_name = 'Tea'
WHERE product_id = 100

A subquery can be nested in the Oracle UPDATE statement. For example, updating the price of the products in category no. 60 so it would match the average price of the products in category 50:

UPDATE products
SET price = (SELECT AVG(price)
		    FROM products
		    WHERE department_id = 50)
WHERE department_id = 60

It is possible to update to a NULL or DEFAULT value.

UPDATE products
SET price = DEFAULT
WHERE product_name = 'Tea'	

UPDATE products
SET price = NULL
WHERE category_id = 80

Executing the Oracle UPDATE statement without using the Oracle WHERE clause results in updating all of the fields in a column.

 

Oracle DML DELETE Statement

The Oracle DELETE statement is used to remove existing rows from a table.

DELETE FROM table_name
WHERE condition

Deleting the row that contains the data of products no. 103:

DELETE FROM products
WHERE product_id = 103

A subquery can be nested in the DELETE statement.

DELETE FROM products
WHERE category_id = (SELECT category_id FROM categories
                       WHERE category_name = 'Sea Food')

• Executing the Oracle DELETE statement without using the Oracle WHERE clause, results in the deletion of all of the rows in the table.
• It is not possible to delete only a single field by using the Oracle DELETE statement .The Oracle DELETE statement deletes rows.
• You can write a Oracle DELETE statement either with or without the Oracle FROM keyword.

DELETE FROM products
-- or
DELETE products

Oracle DML MERGE Statement

The Oracle MERGE statement is used to synchronise the data of two tables, based on differences found between them, if the same row exists in both tables (row with the same customer id for example), but still each row has different values (each table holds a different phone number of that customer), UPDATE operation will be executed. If the row only exists in one table, INSERT operation will be executed.

This sample demonstrates the merge concept. At the end of each month, the data of Customers table is merged with the data of the Backups table. In the beginning of the month, the only MERGE activity was transferring (INSERT) all rows, as is, to the Backup table (provided that this was the first time that the Backup table was populated).

At the end of the month, due to the changes that were carried out in the Customers table, the data items are merged with the Backup table as follows:

• For customers that already exist in the backup table, an UPDATE operation is carried out, for example, George’s Internet Package will be changed from 2.5 MB to 5.0 MB.
• For new customers, an INSERT operation is carried out, thereby adding a new customer – for example, Roy– to the backup table.

Basic Syntax

 MERGE INTO destination_table alias
 USING source_table alias
 ON condition
 WHEN MATCHED THEN
 UPDATE SET
 destination_table_alias.column = source_table_alias.column,
 destination_table_alias.column = source_table_alias.column
 …
 WHEN NOT MATCHED THEN
 INSERT VALUES  (source_table_alias.column, source_table_alias.column ..
                )

For example

MERGE INTO  customers_backup bkup
USING       customers cust
ON         (bkup.cust_id = cust.cust_id)
WHEN MATCHED THEN
UPDATE SET
bkup.cust_name = cust.cust_name ,
bkup.cust_surfing_package = cust.cust_surfing_package
WHEN NOT MATCHED THEN
INSERT VALUES(cust.cust_id , cust.cust_name , cust.cust_surfing_package)

Database Transactions

Transactions are a single unit of various modification commands (such as UPDATE, INSERT, DELETE), which in most cases are associated with a single logical group. The term “a single logical group” refers to a set of operations with logical connection; for example: a batch of DML operations that are meant for updating specific data items in the customers table.

The COMMIT command used to save all changes made by the transaction in the database. The COMMIT command saves all modifications since the last COMMIT or ROLLBACK command.

The ROLLBACK command used to undo changes made by a transaction. The ROLLBACK command can only undo modifications since the last COMMIT or ROLLBACK command that was issued.