In this post I want to discuss about DML statements in SQL.
DML ::
DML means Data Manipulation Language. These statements will be used to modify the data present in the tables. These statements will work on the data, there is no relation for these statements with the structure of the tables. Data manipulation involves Inserting data into tables, modifying the data present in the tables and removing the data from the tables. DML statements require Transaction control.
Any change which is made to the database by a DML statement will be called as a transaction. So, any change made by DML statement needs to be controlled by TCL statements (Transaction Control). I will discuss about these TCL statements in my future posts. In order to handle the changes done to the database by DML statements , we need transaction control.
We have 4 types of DML statements available in the SQL. They are,
1. Insert
2. Delete
3. Update
4. Merge
1. Insert ::
Insert will be used to insert new data into the tables. In other words we can say, Insert will be used to create new rows in the tables. We cannot effect the existing rows from the table using Insert statement.
We have two different syntax's for writing the Insert statement. They are,
1. INSERT INTO tab_name values ( val1, val2, ...... valn);
2. INSERT INTO tab_name (col1, col2, col3, ....., coln) VALUES (val1, val2, ..... , valn);
First syntax is the general syntax of insert statement. In this type of syntax we must pass values to all the columns of the table, and that also in the order of presence of these columns in the table. Otherwise, we will get error. (In this post I don't want to discuss about duplicate rows, not null values... etc. This I will discuss while discussing the constraints of the table).
For Example, Let us say we have a table called EMP in the database. The columns present in this table are Empno, Ename, Sal and Deptno. Now If I want to insert new row into this table using first syntax then,
INSERT INTO Emp VALUES ( 1111, 'RAM', 5000, 20);
Now, I will discuss about the 2nd syntax. In this second syntax we can enter values into the columns of the table while creating new rows in our own desired order irrespective of the order of presence of these columns in the table. This we can achieve by mentioning the our desired order between tab_name and VALUES as shown in the syntax. In this syntax we can also give values to specific columns of the table only. This means, If we have 4 columns in the table and while creating the new row If you want to give values only to 3 columns we can do that by using second syntax.
Ex : INSERT INTO Emp ( Empno, Ename, Sal) Values (2222, 'RAJ', 2000);
We have one more syntax for creating new rows in the table using Insert statement. That is shown below.
INSERT INTO Emp VALUES ( &val1, &val2, ..... &valn).
In this syntax, if we run the above statement then system will ask us to enter the values one by one. after entering all the values row will be created in the table. after that if we hit '/' at the command prompt then system will ask us to enter the values again to create another row. Like this we can create n number of rows.
In this way we can create new rows in the table using different syntax's.
2. Delete ::
Delete will be used to remove the data from the tables. Using Delete we can remove single row or multiple rows from the table. We have two syntax's for Delete statement.
1. DELETE FROM tab_name;
2. DELETE FROM tab_name WHERE condition ;
If we use first syntax then all the rows from the table will be removed. If we want to remove specific rows only then we have to use the second syntax.
Ex : DELETE FROM Emp;
If we execute this statement then all the rows from the EMP table will be removed.
DELETE FROM Emp WHERE empno = 1111;
If we execute this statement then the row belongs to employee with Empno 1111 will be removed. So, using this syntax we can remove specific rows from the table by specifying the Where clause.
Using Delete we can remove entire row from the table only. we cannot remove values from specific columns of the table using Delete statement.
3. Update ::
Update will be used to modify the data present in the tables. using single update statement we can modify the data present in the single column or multiple columns of the table. Similarly, using single update we can modify data present in single row or multiple rows.
Different syntax's for Update statement are,
1. UPDATE tab_name SET col_name = val ;
2. UPDATE tab_name SET col_name = val WHERE condition;
3. UPDATE tab_name SET col1 = val1, col2 = val2 , .... ;
4. UPDATE tab_name SET col1 = val1, col2 = val2,... WHERE condition;
If we use the first syntax, then in all the rows of the table the column specified (col_name) will be updated to the provided value (val).
If we use second syntax, then for the rows which will satisfy the condition specified in the WHERE clause column specified (col_name) will be updated to the provided value(val).
Third and Fourth syntax's are similar to first and second syntax's only , only difference here is instead of updating single column we are updating multiple columns of the table.
EX : UPDATE Emp SET deptno = 20; (This will update deptno to 20 in all the rows of Emp table).
UPDATE Emp SET sal = 5500 WHERE ename = 'RAM'; (This will update sal of Ram to 5500).
UPDATE Emp SET sal = 6000, deptno = 30 ;
UPDATE Emp SET sal = 7000, deptno = 40 WHERE ename = 'RAM';
As I discussed before, If we want to insert value to a particular column of existing row we cannot use Insert statement.We have to use Update statement to update the column to that particular value. Similarly If we want to remove values from specific column then we cannot do it using Delete statement. We have to update that column or columns to null.
Ex : UPDATE Emp SET deptno = null WHERE empno = 3333;
4. Merge ::
Merge statement will be used to combine data from two similar tables into single table. In order to do merge operation two tables containing similar columns are required. Generally we will use merge statement to insert data from one table into other similar table. While doing this If same row is present in both the tables then we cannot perform insert operation due to the constraints of the table. This is where the merge operation is very useful.
Merge is the combination of both Insert and Update operations. So, while merging data from one table into another table, If we have same row in both the tables then we will perform UPDATE operation. If different row is present, then we will perform INSERT operation. This is the concept involved with MERGE statement.
Syntax for writing the merge statement is,
MERGE INTO tab1
USING tab2
ON condition
WHEN MATCHED THEN
UPDATE SET tab1. col = tab2. col
WHEN NOT MATCHED THEN
INSERT VALUES ( tab2. col1, tab2.col2, ........ );
Let me explain this merge statement with an example. Let us say we have two similar tables Emp and Emp1 . I want to merge data from Emp1 to Emp table. Then statement used for this is,
MERGE INTO Emp
USING Emp1
ON emp.empno = emp1.empno
WHEN MATCHED THEN
UPDATE SET emp.sal = emp1.sal
WHEN NOT MATCHED THEN
INSERT VALUES ( emp1.empno, emp1.ename, emp1.sal, emp1.deptno);
So, here I am merging data from Emp1 table into Emp table. ON will be used to check the common thing between the two tables to decide when to do update and when to do Insert. Here using Empno column I am deciding this. Generally we will use primary key columns where unique values will be present to compare in ON condition.
Now if the condition is satisfied then, WHEN MATCHED condition will be satisfied and the UPDATE statement will be performed. Here we are taking sal from emp1 table and updating it to emp table. If condition is not satisfied then WHEN NOT MATCHED condition will be satisfied and new row will be inserted into Emp table from Emp1 table.
So, This is how Merge statement will be worked. So, in case of Merge operation if similar rows are there we will perform Update operation and if different row is there then we will perform Insert operation.
So, these are the different DML statements available in SQL.
DML ::
DML means Data Manipulation Language. These statements will be used to modify the data present in the tables. These statements will work on the data, there is no relation for these statements with the structure of the tables. Data manipulation involves Inserting data into tables, modifying the data present in the tables and removing the data from the tables. DML statements require Transaction control.
Any change which is made to the database by a DML statement will be called as a transaction. So, any change made by DML statement needs to be controlled by TCL statements (Transaction Control). I will discuss about these TCL statements in my future posts. In order to handle the changes done to the database by DML statements , we need transaction control.
We have 4 types of DML statements available in the SQL. They are,
1. Insert
2. Delete
3. Update
4. Merge
1. Insert ::
Insert will be used to insert new data into the tables. In other words we can say, Insert will be used to create new rows in the tables. We cannot effect the existing rows from the table using Insert statement.
We have two different syntax's for writing the Insert statement. They are,
1. INSERT INTO tab_name values ( val1, val2, ...... valn);
2. INSERT INTO tab_name (col1, col2, col3, ....., coln) VALUES (val1, val2, ..... , valn);
First syntax is the general syntax of insert statement. In this type of syntax we must pass values to all the columns of the table, and that also in the order of presence of these columns in the table. Otherwise, we will get error. (In this post I don't want to discuss about duplicate rows, not null values... etc. This I will discuss while discussing the constraints of the table).
For Example, Let us say we have a table called EMP in the database. The columns present in this table are Empno, Ename, Sal and Deptno. Now If I want to insert new row into this table using first syntax then,
INSERT INTO Emp VALUES ( 1111, 'RAM', 5000, 20);
Now, I will discuss about the 2nd syntax. In this second syntax we can enter values into the columns of the table while creating new rows in our own desired order irrespective of the order of presence of these columns in the table. This we can achieve by mentioning the our desired order between tab_name and VALUES as shown in the syntax. In this syntax we can also give values to specific columns of the table only. This means, If we have 4 columns in the table and while creating the new row If you want to give values only to 3 columns we can do that by using second syntax.
Ex : INSERT INTO Emp ( Empno, Ename, Sal) Values (2222, 'RAJ', 2000);
INSERT INTO Emp (Empno, Sal, Ename) Values (3333, 1500, 'KRISH');
We have one more syntax for creating new rows in the table using Insert statement. That is shown below.
INSERT INTO Emp VALUES ( &val1, &val2, ..... &valn).
In this syntax, if we run the above statement then system will ask us to enter the values one by one. after entering all the values row will be created in the table. after that if we hit '/' at the command prompt then system will ask us to enter the values again to create another row. Like this we can create n number of rows.
In this way we can create new rows in the table using different syntax's.
2. Delete ::
Delete will be used to remove the data from the tables. Using Delete we can remove single row or multiple rows from the table. We have two syntax's for Delete statement.
1. DELETE FROM tab_name;
2. DELETE FROM tab_name WHERE condition ;
If we use first syntax then all the rows from the table will be removed. If we want to remove specific rows only then we have to use the second syntax.
Ex : DELETE FROM Emp;
If we execute this statement then all the rows from the EMP table will be removed.
DELETE FROM Emp WHERE empno = 1111;
If we execute this statement then the row belongs to employee with Empno 1111 will be removed. So, using this syntax we can remove specific rows from the table by specifying the Where clause.
Using Delete we can remove entire row from the table only. we cannot remove values from specific columns of the table using Delete statement.
3. Update ::
Update will be used to modify the data present in the tables. using single update statement we can modify the data present in the single column or multiple columns of the table. Similarly, using single update we can modify data present in single row or multiple rows.
Different syntax's for Update statement are,
1. UPDATE tab_name SET col_name = val ;
2. UPDATE tab_name SET col_name = val WHERE condition;
3. UPDATE tab_name SET col1 = val1, col2 = val2 , .... ;
4. UPDATE tab_name SET col1 = val1, col2 = val2,... WHERE condition;
If we use the first syntax, then in all the rows of the table the column specified (col_name) will be updated to the provided value (val).
If we use second syntax, then for the rows which will satisfy the condition specified in the WHERE clause column specified (col_name) will be updated to the provided value(val).
Third and Fourth syntax's are similar to first and second syntax's only , only difference here is instead of updating single column we are updating multiple columns of the table.
EX : UPDATE Emp SET deptno = 20; (This will update deptno to 20 in all the rows of Emp table).
UPDATE Emp SET sal = 5500 WHERE ename = 'RAM'; (This will update sal of Ram to 5500).
UPDATE Emp SET sal = 6000, deptno = 30 ;
UPDATE Emp SET sal = 7000, deptno = 40 WHERE ename = 'RAM';
As I discussed before, If we want to insert value to a particular column of existing row we cannot use Insert statement.We have to use Update statement to update the column to that particular value. Similarly If we want to remove values from specific column then we cannot do it using Delete statement. We have to update that column or columns to null.
Ex : UPDATE Emp SET deptno = null WHERE empno = 3333;
4. Merge ::
Merge statement will be used to combine data from two similar tables into single table. In order to do merge operation two tables containing similar columns are required. Generally we will use merge statement to insert data from one table into other similar table. While doing this If same row is present in both the tables then we cannot perform insert operation due to the constraints of the table. This is where the merge operation is very useful.
Merge is the combination of both Insert and Update operations. So, while merging data from one table into another table, If we have same row in both the tables then we will perform UPDATE operation. If different row is present, then we will perform INSERT operation. This is the concept involved with MERGE statement.
Syntax for writing the merge statement is,
MERGE INTO tab1
USING tab2
ON condition
WHEN MATCHED THEN
UPDATE SET tab1. col = tab2. col
WHEN NOT MATCHED THEN
INSERT VALUES ( tab2. col1, tab2.col2, ........ );
Let me explain this merge statement with an example. Let us say we have two similar tables Emp and Emp1 . I want to merge data from Emp1 to Emp table. Then statement used for this is,
MERGE INTO Emp
USING Emp1
ON emp.empno = emp1.empno
WHEN MATCHED THEN
UPDATE SET emp.sal = emp1.sal
WHEN NOT MATCHED THEN
INSERT VALUES ( emp1.empno, emp1.ename, emp1.sal, emp1.deptno);
So, here I am merging data from Emp1 table into Emp table. ON will be used to check the common thing between the two tables to decide when to do update and when to do Insert. Here using Empno column I am deciding this. Generally we will use primary key columns where unique values will be present to compare in ON condition.
Now if the condition is satisfied then, WHEN MATCHED condition will be satisfied and the UPDATE statement will be performed. Here we are taking sal from emp1 table and updating it to emp table. If condition is not satisfied then WHEN NOT MATCHED condition will be satisfied and new row will be inserted into Emp table from Emp1 table.
So, This is how Merge statement will be worked. So, in case of Merge operation if similar rows are there we will perform Update operation and if different row is there then we will perform Insert operation.
So, these are the different DML statements available in SQL.
