Oracle - Data Manipulation Language

Hi Everyone,

Here are are the basic Oracle data manipulation statements.

INSERT Command: Method 1

INSERT INTO <table name> (col1, [col2, col3, ... ]) VALUES (val1,[val2, val3, ...]);

This method is used when we are not sure about the order of occurance of table fields.

INSERT INTO dept (deptname, deptid) VALUES ('Software',1);

INSERT INTO emp (empid, deptid, empname, salary) VALUES (1,1,'Shibin',1000);

INSERT Command: Method 2

INSERT INTO <table name> VALUES (val1, [val2, val3, ...]);

This method can be adopted when the order of occurrence of table fields are known.

INSERT INTO emp VALUES (2,'Shine',1,2500.00);

INSERT Command: Method 3

INSERT INTO <table name> VALUES (&field1, ['&field2', &field3, &field4, ...]);

• The field values can be entered dynamically
• field1, field2, etc are the variables that represents the table fields as it occurs in the table.
• character and date fields should be enclosed within single quotes

INSERT INTO emp VALUES (&empid,'&empname',&deptid,&salary);

INSERT Command: Method 4

INSERT INTO <table name> SELECT Statement

Inserting into a table by selecting records from the other table.

INSERT INTO  emp SELECT * FROM sample

UPDATE Command

UPDATE <table name> SET <col1> = <exp1>[, <col2> = <exp2>, <col3> = <exp3>, ...]  WHERE <condition1>, [<condition2>, <condition3>, ...];

UPDATE emp1 SET empname = 'shibin varghese' WHERE empid=1;

Complex UPDATE statement

• Cant reference more than one table in an UPDATE statement
• Can make use of EXISTS clause to use UPDATE A TABLE based on other table value
• Below DML statement updates the empname field of emp table by fetching the empname from sample table.
• It also compare the empname of emp table and empname of sample table, if they are not equal the empname field of emp table will be updated.

UPDATE emp e

SET empname=(SELECT s.empname FROM sample s WHERE e.empid=s.empid)

WHERE EXISTS (SELECT e.empname FROM sample s WHERE e.empid=s.empid AND e.empname<>s.empname)

DELETE Statement

DELETE FROM <Table Name> WHERE <condition1, [condition2, condition3, ...]>

DELETE FROM emp WHERE empid=1;

Complex DELETE Statement

Deletes the records from sample table where empname exists in emp table.

DELETE FROM sample s

WHERE EXISTS (SELECT e.empname FROM emp e WHERE e.empname=s.empname);

Deleting duplicate records

DELETE FROM sample

WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM sample GROUP BY empname);

Thanks for reading Blog !