DML Commands in oracle

DML means Data Manipulation Language. Data manipulation commands are used to manage data. Using DML command, you can create new records, update records and delete records.

DML Commands is divided into three categories as follows:
1. INSERT
2. UPDATE
3. DELETE

1. INSERT

To insert new record in table and stores the values in respective columns of table, this statement is used.

To insert record in table by specifying column names

Syntax

INSERT INTO tablename (columnname1,columnname2,........,columnnameN)
VALUES (expression1,expression2,........,expressionN);

In the above syntax INSERT, INTO and VALUES are keywords, in place of tablename you have to write your table name.
In brackets write columnname of your table and then provide values in place of expression with respect to the columns.

To insert record in table without specifyng column names

Syntax

INSERT INTO tablename
VALUES (expression1,expression2,........,expressionN);
In the above syntax INSERT, INTO and VALUES are keywords, in place of tablename you have to write your table name.
In brackets write values in place of expression with respect to the columns.

2. UPDATE

To change or modify data values of any table we have to use UPDATE command.
It will update only specific rows in table when it used with WHERE clause, in which we are specify our filter condition otherwise update all rows of table.

To update specific row in table using where clause

Syntax

UPDATE tablename SET columnname1=’expression1’ , columnname2=’expression2’
WHERE condition;

In the above syntax UPDATE, SET and WHERE are keywords, in place of tablename you have to write your table name.
In place of columnname write name of column whose values is require to be updated, in place of expression write values which is to be updated and condition will be replaced by filtering condition after WHERE clause.

To update all rows in table

Syntax
UPDATE tablename SET columnname1=’expression1’ , columnname2=’expression2’;

In the above syntax UPDATE and SET are keywords, in place of tablename you have to write your table name.
In place of columnname write name of column whose values is require to be updated and in place of expression write values which is to be updated.

3. DELETE

To remove specific row or all rows from table in database we have to DELETE command.

To delete specific row from table using where clause

Syntax

DELETE FROM tablename
WHERE condition;

In the above syntax DELETE, FROM and WHERE are keywords, in place of tablename you have to write your table name. To delete all rows from tables

Syntax

DELETE FROM tablename;

In the above syntax DELETE and FROM are keywords, in place of tablename you have to write your table name.

Now we will study above commands with example.

Example

Creating customer table

Inserting first row in table

Selecting table data

Inserting second row in table

Selecting table data

NOTE:- Now we are inserting row with different way in which we do not require to specify name of columns, but keep in mind that sequences of column values and all columns values to be passed while inserting.

Inserting third row in table

Selecting table data

Inserting fourth row in table

Selecting table data

After inserting data with two different ways now we will see about updating data in table.
NOTE:- It is very important to specify where condition in update and delete query otherwise all rows will be updated or deleted.

Updating row without where condition