Data Manipulation Language

This page summarizes how to:

Use data manipulation language commands.

The Data Manipulation language is used for query, insertion, updating and deletion of data stored in the database. This includes commands like SELECT, INSERT, UPDATE, DELETE and MERGE.

The INSERT statement

    INSERT INTO tablename [ ( column1,column2,..)]
    VALUES (valuel,value2 ... );

Sample Query

SQL> INSERT INTO emp VALUES
    (7900, 'STEVE', 'PROGRAMMER',7369,'08-JUN-98',3000,NULL,20);

Inserting results of a query

This command is used to copy rows partially from one table to another.

SQL> INSERT INTO bonus (ename, bonusAmout, bonusdate) 
     SELECT ename, sal*0.1, sysdate
     FROM emp
     WHERE job = 'SALESMAN';

The MERGE statement

MERGE statement is used to select rows from one table for insertion or update rows in to another table based on given condition.

    MERGE INTO emp e USING emp_temp t
    ON (e.empno = t.empno)
    WHEN MATCHED THEN UPDATE SET e.job = t.job, e.sal = t.sal,
    e.deptno = t.deptno) 
    WHEN NOT MATCHED THEN INSERT
    VALUES (t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptno);

The UPDATE statement

SQL> UPDATE emp
     SET job = 'MANAGER'
     WHERE ename = 'MILLER';

Modifying values columns

SQL> UPDATE emp
     SET sal = sal* 1.1, comm =0.15 *sal
     WHERE deptno =30 and comm is NULL;

Deleting rows from a table

SQL> DELETE FROM emp
     WHERE ename = 'JONES';

Transaction Commands

Save changes - COMMIT

We can make the changes permanent in the database by using the COMMIT command. The syntax is

    C0MMIT [WORK ] ;

Undoing changes - ROLLBACK

The syntax is

    ROLLBACK [ WORK ] ;

#The INSERT statement

#Inserting results of a query

#The MERGE statement

#The UPDATE statement

#Modifying values columns

#Deleting rows from a table

#Transaction Commands

#Save changes - COMMIT

#Undoing changes - ROLLBACK