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 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.
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);
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.
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.
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.
Updating the salary and setting it to 8500.
Now see the updated value of SALARY.
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;
DELETE Statement in Oracle
DELETE statement is used to delete rows from a table.
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.