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
- 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.
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:
- 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)
- The order of the values must match the order of the columns in the column list. In the case above, the first value must be associated with the product number; the second value must be associated with the product name, and so on.
- The value type must match the type of columns in the column list. 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 column list. Therefore, there is no need to enter a value into each column of the table.
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 …
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
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.
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 .. )
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)
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.