DML Statements in Oracle
What are DML Statements in Oracle:
The DML statements are used to perform the manipulation on the data. DML stands for Data Manipulation Language which is used to perform operations on data.
Following is the list of DML statements:
INSERT INTO Statement:
The INSERT INTO query or a DML statement is used to insert or add new rows or records to the table.
Syntax to insert single Row:
INSERT INTO Tablename
Values(value, value, value, value);
INSERT INTO keyword is used to insert a new record or row to the table. Replace tablename with your tablename, Values keyword is used to insert the values and in brackets write the actual values for all the columns.
For example,
SQL> insert into student
2 values('BCA05','Rupesh','BCA','A',23,50);
1 row created.
1 row is stored to student table.
Another Syntax:
INSERT INTO Tablename
(Column1, column2, column3)
Values(value, value, value);
For example,
SQL> insert into student
2 (Rollno, Name, Class, Div)
3 values('BCA06','Rupali','MCA','C');
1 row created.
SQL> select * from student;
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 26 65
BCA02 Seeta BBA A 28 66
BCA03 Rohan BA C 22 45
BCA04 Rakesh BA C 24 55
BCA05 Rupesh BCA A 23 50
BCA06 Rupali MCA C
6 rows selected.
So in the above Insertion query only Rollno, Name, Class and Div column are selected to insert new rows. So you will be allowed only these columns to store new rows, not all the columns, because specific columns are selected to insert the new row.
Syntax to INSERT Multiple Rows to the Table:
Syntax:
INSERT All
INTO Tablename Values(Value, Value, Value)
INTO Tablename Values(value, value, value)
………………………………………………………
Select * From Dual;
To insert multiple rows in your table first use INSERT ALL keyword then use INTO keyword followed by Tablename and Values Keyword and in parenthesis insert values, Same process repeat for second row, third row and so for Nth row. End your query with Select * from Dual the dummy table to store all the rows and then select all the rows.
For example,
SQL> insert all
2 into student values('MCA01', 'Suhani','MCA','A',25,59)
3 into student values('MCA02', 'Samoksha','MCA','A',29,69)
4 into student values('MBA03', 'Radha','MBA','A',30,69)
5 select * from Dual;
3 rows created.
SQL> select * from student;
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 26 65
BCA02 Seeta BBA A 28 66
BCA03 Rohan BA C 22 45
BCA04 Rakesh BA C 24 55
BCA05 Rupesh BCA A 23 50
BCA06 Rupali MCA C
MCA01 Suhani MCA A 25 59
MCA02 Samoksha MCA A 29 69
MBA03 Radha MBA A 30 69
9 rows selected.
2. UPDATE SET:
Update Set is DML statement used for updating the information in a table.
Syntax:
Update Tablename
Set Columnname=Value
Where Condition;
For Example:
Update Student
Set Name=’Sakshi’
Where Rollno=10;
Here the Student table is updated. The new value is set to the column Name and the modified value is ‘Sakshi’. This value is set to the student whose Rollno is 10. The condition is required in case you have many rows and you want to update the value of a particular column in between all the rows. So as per the condition given the value will be set for a particular column.
The Update set statement is also used to insert the value for a column in case you do not have value in a table for a particular row.
For example in following table Address column is newly added and it ha no rows in that column.
SQL> select * From student;
ROLLNO NAME CLASS DI ADDRESS
---------- ---------- ---------- -- ---------------
1 MAnisha BCAI A
2 Monisa BCAII B
3 Mahesh BCAIII C
4 Manthan BBAIII C
So to fill the data for Address column you can write query as follows
SQL> update student
2 set Address ='Pune'
3 where Rollno=1;
1 row updated.
SQL> select * from Student;
ROLLNO NAME CLASS DI ADDRESS
---------- ---------- ---------- -- ---------------
1 MAnisha BCAI A Pune
2 Monisa BCAII B
3 Mahesh BCAIII C
4 Manthan BBAIII C
SQL> update student
2 set Address ='Mumbai'
3 where Rollno=2;
1 row updated.
SQL> select * from student;
ROLLNO NAME CLASS DI ADDRESS
---------- ---------- ---------- -- ---------------
1 MAnisha BCAI A Pune
2 Monisa BCAII B Mumbai
3 Mahesh BCAIII C
4 Manthan BBAIII C
So in this way you can use the Update Set statement to insert rows for newly added columns.
3. Delete Statement:
The delete statement is used to delete all the rows from the table or a particular row from a table.
Syntax:
To Delete all Rows
Delete From Tablename;
For Example:
Delete From Student;
This query will delete all the rows from the student table.
To Delete row for particular columns
For Example;
Following table is used to delete a particular row.
SQL> select * from result;
ROLLNO INT_MARKS EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
1 29 67 A Pass
2 26 65 A Pass
3 23 55 B Pass
4 23 45 C Pass
SQL> delete from result
2 where rollno=1;
1 row deleted.
SQL> select * from result;
ROLLNO INT_MARKS EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
2 26 65 A Pass
3 23 55 B Pass
4 23 45 C Pass
Entire row is deleted from the Result table whose roll no is 1.
Important note:
If you have parent and child tables. I.e. If you have Referential Tables then remember you cannot delete the data from the parent table first. It will show the error.
To avoid the error, first delete data from the child table and then delete the data from the parent table.
For Example:
SQL> delete from student
2 where rollno=01;
delete from student
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.SYS_C008413) violated - child record
The above query has an error because the Rollono column in the student table is Primary Key and in the Result table it has Foreign Key. You cannot delete the data directly from your Parent table. First delete it from the child table and then delete it from the parent table.
First deleted from Result table,
SQL> delete from result
2 where rollno=1;
1 row deleted.
SQL> select * from result;
ROLLNO INT_MARKS EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
2 26 65 A Pass
3 23 55 B Pass
4 23 45 C Pass
One row is deleted.
Now delete the row from the student table. It will allow you.
SQL> delete from student
2 where rollno=1;
1 row deleted.
SQL> select * from student;
ROLLNO NAME CLASS DI ADDRESS
---------- ---------- ---------- -- ---------------
2 Monisa BCAII B Mumbai
3 Mahesh BCAIII C
4 Manthan BBAIII C
Now one row is deleted from the student table whose Rpllno is 1. The Delete is possible only when you delete the row from Result table whose Rollno is 1.
0 टिप्पण्या
कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏