SQL Tutorial on Data Manipulation Language (DML)

After writing a tutorial on SQL Baiscs, long back, here I am writing again on SQL, but this time on DML i.e. Data Manipulation Language.

By the full form of DML we understand that with the help of this we will be able to manipulate data. Now what kind of manipulation can we do might be the next question in your mind, so SQL supports manipulation like INSERT, UPDATE, DELETE and MERGE. 

INSERT 

The INSERT command in SQL is used to add records into an existing table.

The INSERT command has two types of syntax, i.e. it can be written in two ways, here are the two ways :

SYNTAX

First way is :

INSERT INTO table_name 

VALUES (value1, value2, value3,...)

Second way :

INSERT INTO table_name(column1, column2, column3,...) 

VALUES (value1, value2, value3,...)

INSERT Example

We have the following "Persons" table:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai

Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

INSERT INTO Persons 

VALUES (4,'J', 'R', 'ANDHERI W', 'MUMBAI')

The "Persons" table will now look like this:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai
4	J	R	Andheri W	Mumbai

Now using the 2nd way, i.e. inserting values into specified columns. 

The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the "FirstName" columns:

INSERT INTO Persons (P_Id, LastName, FirstName) 

VALUES (5, 'Mark', 'Patel')

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai
4	J	R	Andheri W	Mumbai
5	Mark	Patel

UPDATE 

The UPDATE command can be used to modify the records in the existing table, either in bulk or individually.

SYNTAX

UPDATE table_name

SET column1=value, column2=value2,...

WHERE some_column=some_value

NOTE : As I mention above that update can modify either a single record or a bulk record. So the where  clause in the update statement helps us to do that. If we use a where clause then whenever the condition in true the record will get updates and if don't use the where clause then all the records are appended.

UPDATE EXAMPLE

 We have the following "Persons" table:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai
4	J	R	Andheri W	Mumbai
5	Mark	Patel

Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

UPDATE Persons 

SET Address='Lower Parel', City='Mumbai'

WHERE LastName='Patel' AND FirstName='Mark'

The "Persons" table will now look like this:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai
4	J	R	Andheri W	Mumbai
5	Mark	Patel	Lower Parel	Mumbai

Important Note :

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Lower Parel	Mumbai
2	Sahil	Shah	Lower Parel	Mumbai
3	Harsh	Gupta	Lower Parel	Mumbai
4	J	R	Lower Parel	Mumbai
5	Mark	Patel	Lower Parel	Mumbai

DELETE

The DELETE command is used to remove records from an existing table. It can only delete row(s) and not column(s).

SYNTAX

DELETE FROM table_name

WHERE some_column=some_value

Note : The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

DELETE Example

The "Persons" table:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai
4	J	R	Andheri W	Mumbai
5	Mark	Patel	Lower Parel	Mumbai

Now we want to delete the person "Patel, Mark" in the "Persons" table.

We use the following SQL statement:

DELETE FROM Persons

WHERE LastName='Patel' AND FirstName='Mark'

The "Persons" table will now look like this:

P_Id	FirstName	LastName	Address	City
1	Raj	Kumar	Vile Parle	Mumbai
2	Sahil	Shah	Babar Road	New Delhi
3	Harsh	Gupta	S.V. Road	Mumbai
4	J	R	Andheri W	Mumbai

MERGE

MERGE statements was introduced in Oracle 9i . This command was beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow and for the performance improvement. Oracle claim it will improve the performance.

SYNTAX

MERGE INTO <table_name>
USING      <table_or_view>
ON         (<condition>)
WHEN MATCHED THEN 
           <UPDATE SET table_name.col_name =  table_or_view.col_name> 
WHEN NOT MATCHED THEN 
           <INSERT (clo_list) VALUES (value_list)>

---

NOTE : Don't forget to COMMIT after every insertion, updation or deletion of records otherwise the action won't become permanent and the table will get locked.