Data Manipulation Language (DML) (INSERT, UPDATE, DELETE, MERGE)

Data Manipulation Language (DML) (INSERT, UPDATE, DELETE, MERGE)

- Inserting rows to dept table

SQL> insert into dept (deptno,dname,loc) values (50,'DDDD','dddd') ;
SQL> insert into dept (deptno,dname,loc) values (&deptno, &dname, &loc) ;
SQL> insert into dept values (50,'DDDD','dddd') ;

Copy the record from dept to dept1 table
SQL> insert into dept select * from dept1 ;
SQL> insert into dept select * from dept1 where deptno=30 ;


Updating/Changing the existing employee’s information
SQL> update emp set sal=sal*2 ;
SQL> update emp set sal=sal*2 where deptno=40 and sal > 2000 ;
SQL> update emp1 set sal = sal * 2 where deptno in (select deptno from emp1 where deptno=20) ;

- Deleting all rows from emp1 table
SQL> delete from emp1 ;
Or
SQL> delete emp1;

Delete the rows from emp1 table whose depart no is 20

SQL> delete from emp1 where deptno = 20 ;

Delete with subqury
SQL> delete from emp1 where deptno in (select deptno from emp1 where deptno=30) ;

- Merging Rows
create table copy_emp
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
SAL NUMBER(7,2),
DEPTNO NUMBER(2)) ;

Merge into copy_emp c using emp1 e on (c.empno= e.empno)
WHEN MATCHED THEN
UPDATE SET C.ename = e.ename , c.sal= e.sal, c.deptno = e.deptno
WHEN NOT MATCHED THEN
INSERT VALUES (e.empno,e.ename,e.sal,e.deptno);