Oracle Apps Modules

DML Statements in Oracle 12c – Insert, Update and Delete

DML Statements in Oracle 12c – Insert, Update and Delete


Data Manipulation Statements are used to manipulate data into existing tables.

The effect of DML statement is not permanent until you commit the transaction.

A transaction is sequence of sql statements that database treats as a unit. Transaction can be rolled back, until it is committed. You will read about Transaction Control Statements in detail in next tutorial.

There are three types of DML Statements:

  • Insert
  • Update
  • Delete

Insert statement in Oracle 12c


Insert statement is used to insert rows into existing tables.

INSERT INTO table_name (col1, col2, ……, coln)
VALUES (val1, val2, ……, valn);

Insert a row in Employees table in HR schema.

To do so first see the structure of Employees table, so you can insert data accordingly.

DESCRIBE Employees;

DML Statements in Oracle 12c – Insert, Update and Delete

Run below query to insert a row in Employees table:

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES (10, 'George', 'Gordon', 'GGORDON', '650.506.2222', '01-JAN-07', 'SA_REP', 9000, .1, 148, 80);

DML Statements in Oracle 12c – Insert, Update and Delete

Inserting specific column’s values


Inserting values when not all column’s values are available. But keep one thing in mind, insert statement cannot be completed without values which are having not null constraint.

In below query we are not inserting SALARY.

DML Statements in Oracle 12c – Insert, Update and Delete

Note: you need to maintain the order of values with respect to columns name while inserting values. Otherwise you will get ORA-01400: cannot insert NULL …..

Update statement in Oracle 12c


Update statement is used to update (change) one or more rows.

Syntax:

UPDATE table_name
SET col_name = value [, col_name = value]...
[WHERE condition];

Retrieving the SALARY of the EMPLOYEE where Last_Name = ‘Keats’

SALARY is null.

DML Statements in Oracle 12c – Insert, Update and Delete

Updating the salary and setting it to 8500.

DML Statements in Oracle 12c – Insert, Update and Delete

Now see the updated value of SALARY.

DML Statements in Oracle 12c – Insert, Update and Delete

Updating multiple rows using UPDATE Statement


Update the commission percentage of the EMPLOYEES whose department ID is equal to 80.

UPDATE EMPLOYEES
SET COMMISSION_PCT = COMMISSION_PCT + 0.05
WHERE DEPARTMENT_ID = 80;

DML Statements in Oracle 12c – Insert, Update and Delete

DELETE Statement in Oracle


DELETE statement is used to delete rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

WHERE condition is used to delete only those rows which satisfy the condition. If you will not use where condition here, it will delete all the columns from the table but empty table will still exits.

Note: To delete a table, use DROP TABLE Statement.

DELETE FROM EMPLOYEES
WHERE HIRE_DATE = TO_DATE('01-JAN-07', 'dd-mon-yy');

Hence you finished the tutorial DML Statements in Oracle 12c: Insert, Update and Delete.

In next tutorial you will read about Transaction Control Statement.

Leave a Reply