DML Commands in oracle
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
Selecting table data
Updating first row
Selecting table data
Updating second row
Selecting table data
Updating third row
Selecting table data
Updating fourth row
Selecting table data
After updating data in table now we will see about deleting data in table.
Deleting particular row from table using where condition
Selecting table data
Deleting all row from table
Selecting table data
I hope this article will help you.