Data Manipulation Language Commands in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Data Manipulation Language (DML) Commands in Oracle with Examples

In this article, I am going to discuss Data Manipulation Language (DML) Commands in Oracle with Examples. Please read our previous article, where we discussed Data Definition Language (DDL) Commands in Oracle with Examples.

Data Manipulation Language (DML) Commands in Oracle:

DML stands for Data Manipulation Language. DML commands are basically used to INSERT, UPDATE, and DELETE data in a database table. That means DML statements affect the records in a table. These are the basic operations that we perform on data such as inserting new records, deleting unnecessary records, and updating/modifying existing records.

DML provides the following three commands:

1. INSERT – To insert new records
2. UPDATE – To update/Modify existing records
3. DELETE – To delete existing records

The following two are the Latest DML Commands in the Oracle database.

1. INSERT ALL
2. MERGE

Example to Understand Data Manipulation Language Commands in Oracle

Let us understand each DML command in Oracle Database with examples. We are going to use the following Employee table to understand the DML statements in Oracle. Please execute the below SQL Statements to create the Employee table.

CREATE TABLE Employee
( 
    Id INT, 
    Name  CHAR(100), 
    Salary NUMBER(8, 2) 
);

First, open SQL Plus editor and then log in to the database and then execute the above CREATE table statement to Create the EMPLOYEE table as shown in the below image.

So, we have created the required Employee table. Now, we will see how to INSERT, UPDATE, and DELETE records from the Employee table.

INSERT DML Command in Oracle:

The INSERT DML command is used to insert a new row into a table. The syntax to use the INSERT command is given below. In the below syntax, we are not specifying the column names, so we need to supply values for all the columns of the specified table. The value sequence in the query must be the same as the column sequence in the table.

Syntax1: INSERT INTO <TableName> VALUES(V1,V2,V3,……….);

Example: Let us insert a record into the EMPLOYEE table. To do so execute the following INSERT query. As you can see, we are providing the values for all the three columns in the same sequence they are defined in the Employee table i.e. Id, Name, and Salary. The sequence is important in this case.

INSERT INTO EMPLOYEE VALUES (1, ‘Anurag’, 50000);

Let us execute the above code in the SQL Plus editor as shown in the below image. You can see we are getting the “1 row created” message which means 1 row is inserted into the Employee table.

Note: Number of columns in a table = Number of values in a query.

Syntax2: In the below syntax we have specified the column names explicitly and we need to provide the values for the specified columns only.

INSERT INTO <Table Name> (REQUIRED COLUMN NAMES) VALUES (V1, V2,……..);

Example: Let us insert another record into the Employee table by executing the below INSERT statement. Here, as you can see, we are specifying the column names and in the same column name sequence we are providing the values i.e. Id, Salary, and Name

INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (2, 35000, ‘Mohanty’);

Let us execute the above code in the SQL Plus editor as shown in the below image. You can see, we are getting the “1 row created” message which means 1 row is inserted into the Employee table.

Example: In the below example, we are providing the Id and Name only. In this case, the Salary column will take the NULL value.

INSERT INTO EMPLOYEE (Id, Name) VALUES (3, ‘Sambit’);

Let us execute the above code in the SQL Plus editor as shown in the below image. You can see, we are getting the “1 row created” message which means 1 row is inserted into the Employee table.

Note: In this method, the user needs to Insert values for required columns only and the remaining columns of the table will take “NULL” by default in Oracle.

How to insert NULLs into a table?

Method1:
INSERT INTO EMPLOYEE VALUES (NULL, NULL, NULL);
Method2:
INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (NULL, NULL, NULL);

How to insert multiple rows into a table?

We need to use the following two substitutional operators:

1. &: to insert values to columns dynamically (we can change values)
2. &&: to insert fixed values to columns (we cannot change values). We can insert values to columns in a fixed manner. If we want to change a fixed value of a column then we should “exit” from the oracle database.  

Syntax1(&): For all columns

The following is the syntax to use & to insert values to columns dynamically for all the columns of a table.

INSERT INTO <TABLE NAME> VALUES(&<COL1>, &<COL2>,………….);

Example:

Now execute the following query in the SQL Plus editor.

INSERT INTO EMPLOYEE VALUES (&Id, ‘&Name’, &Salary);

Once you execute the above code in SQL Plus editor, then it will ask you to enter the Id, Name, and Salary column field values one by one as shown in the below image.

If you want to insert another row into the Employee table, then simply type / and press the enter key in the SQL Plus editor. The / is used to re-execute the last executed SQL query in SQL Plus editor. Once you type the / button and press the enter key then again it will ask you to enter the Id, Name, and salary column values one by one as shown in the below image.

Syntax2(&): For required columns

The following is the syntax to use & to insert values to columns dynamically for the required columns of a table.

INSERT INTO <TABLE NAME >(REQUIRED COLUMN NAMES) VALUES (&<COL1>,&<COL2>,…….);

Example:

Now execute the following query in the SQL Plus editor to insert a record with Id and Name column values, not the Salary column.

INSERT INTO EMPLOYEE(Id, Name) VALUES (&Id, ‘&Name’);

Once you execute the above code in SQL Plus editor, then it will ask you to enter the Id, and Name column field values one by one as shown in the below image.

If you want to insert another row into the Employee table with the Id and Name column values, then simply type / and press the enter button in the SQL Plus editor. The / button is used to re-execute the last executed SQL query in SQL Plus editor. Once you type the / button and press the enter key then again it will ask you to enter the Id, and Name column values one by one as shown in the below image.

Syntax1(&&): For all Columns

The following is the syntax to use “&&” to insert fixed values to columns for all the columns of a table. In this case, we cannot change values.

INSERT INTO <TABLE NAME> VALUES(&&<COL1>, &&<COL2>,………….);

Example: Now execute the following query in the SQL Plus editor.

INSERT INTO EMPLOYEE VALUES (&&Id, ‘&&Name’, &&Salary);

Once you execute the above code in SQL Plus editor, then it will ask you to enter the Id, Name, and Salary column field values one by one as shown in the below image.

Now, if you type / and press the enter key, then it will insert the same record once more into the Employee table as shown in the below image. That means it is going to insert fixed values. The value cannot be changed dynamically.

UPDATE DML statement in Oracle:

The UPDATE DML Statement in Oracle is basically used to

1. To update all rows data in a table at a time.
2. To update a specific row data in a table by using the “where” condition.

The following is the syntax to use the UPDATE DML statement in Oracle.

UPDATE <TABLE NAME> SET <COLUMN NAME1>=<VALUE1>, <COLUMN NAME2>=<VALUE2>, ……..[ WHERE <CONDITION> ];

Example:

Update the Name and Salary of the Employee whose Id is 3. The following Update SQL Script will update the same in the Employee table.

UPDATE EMPLOYEE SET Name=’Test1′, Salary=55000 WHERE Id=3;

When you execute the above query, you will get the message as 1 row updated as shown in the below image as there is only one employee in the Employee table whose id is 3.

Update the Salary of the Employee whose Name is Anurag. The following Update SQL Script will update the same in the Employee table.

UPDATE EMPLOYEE SET Salary=85000 WHERE Name=’Anurag’;

When you execute the above UPDATE DML statement, you will get the message as 1 row updated as shown in the below image as there is only one employee in the Employee table whose name is Anurag.

Update the Salary of all the Employees to 500000. The following Update SQL Script will update the same in the Employee table.

UPDATE EMPLOYEE SET Salary =500000;

When you execute the above UPDATE statement, you will get the message as 10 rows updated as shown in the below image as in the Employee table total of 10 records are there and we are not applying any filter.

DELETE DML statement in Oracle:

The DELETE DML Statement in Oracle is basically used to

1. To delete all rows from a table at a time.
2. To delete a specific row from a table by using the “where” condition.

The syntax to use the UPDATE DML statement in Oracle is given below.

DELETE FROM <TABLE NAME> [ WHERE <CONDITION> ];

Example:

Delete the Employee record from the Employee table whose id is 5. The following DELETE SQL Script will update the same in the Employee table.

DELETE FROM EMPLOYEE WHERE Id=5;

When you execute the above DELETE query, you will get the message as 1 row deleted as shown in the below image as there is only one employee in the Employee table whose id is 5.

If you want to DELETE all the employees from the Employee table, then you need to use the DELETE statement as follows without any conditions.

DELETE FROM EMPLOYEE;

When you execute the above DELETE DML statement, then you will get the message as 9 rows deleted as shown in the below image as in the Employee table total 9 records are there and we are not applying any filter.

Delete VS Truncate in Oracle:

Delete                                                             

1. It is a DML command
2. It can delete a specific row from a table.
3. It supports the “WHERE” clause condition.
4. It is a temporary data deletion.
5. We can restore deleted data by using the “ROLLBACK” command.
6. Execution speed is slow as deleting operation is performed row by row / one by one manner.

Truncate

1. It is a DDL command
2. It is not possible to delete a specific row from a table.
3. It does not support the “WHERE” clause condition.
4. It is a permanent data deletion.
5. We cannot restore deleted data by using the “ROLLBACK” command.
6. Execution speed is fast as deleting a group of rows at a time.

New DML Commands in Oracle

The following are New DML Commands introduced in Oracle for DML operations:

1. Insert all
2. Merge

INSERT ALL Command in Oracle:

The INSERT ALL statement in Oracle is basically used to add multiple rows with a single INSERT statement. The important point that you need to remember is, the rows can be inserted into one table or multiple tables using only one SQL command. The syntax for the INSERT ALL statement in Oracle is shown in the below image:

Parameters or Arguments

1. table_name: It specifies the name of the table in which you want to insert records.
2. column1, column2, column_n: This specifies the name of the columns in the table to insert values.
3. expr1, expr2, expr_n: This specifies the values to assign to the columns in the table.

Example: Insert into One Table

The following example specifies how to insert multiple records into one table. Here we are inserting three records into the EMPLOYEE table.

INSERT ALL
  INTO EMPLOYEE (Id, Salary, Name) VALUES (1, 35000, 'Pranaya')
  INTO EMPLOYEE (Id, Salary, Name) VALUES (2, 45000, 'Kumar')
  INTO EMPLOYEE (Id, Salary, Name) VALUES (3, 55000, 'Rout')
SELECT * FROM dual;

Now, let us execute the above code in SQL Plus editor as shown in the below image. You can see, here we are getting the message as 3 rows created. That means three records are inserted into the Employee table.

The above INSERT ALL query is equivalent to the following three INSERT statements.

INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (1, 35000, 'Pranaya');
INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (2, 45000, 'Kumar');
INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (3, 55000, 'Rout');

Example: Insert into Multiple Table

As of now, we have only one table i.e. EMPLOYEE. Now, let us create another table i.e. ADDRESS by executing the below CREATE table statement.

CREATE TABLE Address
(
  Id INT Primary Key,
  EmpId INT NOT NULL,
  Addrees VARCHAR(100)
);

Now, execute the above CREATE TABLE statement in SQL Plus editor as shown in the below image. Here, you can see, we are getting the “Table Created” message means the Address table is created.

It is also possible in Oracle to use the INSERT ALL statement to insert multiple rows into multiple tables in one command. For example, let us insert records into both the EMPLOYEE and ADDRESS table by executing the following SQL statement.

INSERT ALL
  INTO EMPLOYEE (Id, Salary, Name) VALUES (4, 35000, 'Pranaya')
  INTO EMPLOYEE (Id, Salary, Name) VALUES (5, 45000, 'Kumar')
  INTO ADDRESS (Id, EmpId, Addrees) VALUES (1, 4, 'BBSR')
  INTO ADDRESS (Id, EmpId, Addrees) VALUES (2, 5, 'CTC')
SELECT * FROM dual;

Now, let us execute the above code in SQL Plus editor as shown in the below image. You can see, here we are getting the message as 4 rows created. That means 2 records are inserted into the Employee table as well as 2 records are inserted into the Address table.

The above INSERT ALL statement is equivalent to the following 4 INSERT statements.

INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (4, 35000, 'Pranaya');
INSERT INTO EMPLOYEE (Id, Salary, Name) VALUES (5, 45000, 'Kumar');
INSERT INTO ADDRESS (Id, EmpId, Addrees) VALUES (1, 4, 'BBSR');
INSERT INTO ADDRESS (Id, EmpId, Addrees) VALUES (2, 5, 'CTC');

Note: In our upcoming articles, we will discuss the MERGE statement in detail with Examples.

In the next article, I am going to discuss Data Retrieval Language (DRL) Command in Oracle with Examples. Here, in this article, I try to explain Data Manipulation Language (DML) Commands in Oracle with Examples and I hope you enjoy this Data Manipulation Language (DML) Commands in Oracle with Examples article.