Agenda

DML – Data Manipulation Language

1. INSERT
2. UPDATE
3. DELETE
4. TRUNCATE

INSERT: It is used to insert values for columns in exist table.

Example: 1. Insert values for all columns
2. Insert values for selected columns
3. Insert values using &
4. Insert values from one table to another table ex. insert values from emp_old to emp_new table

SQL> Select * from emp;

SQL> create table emp_new_78 (eno number, ename varchar2(10), sal number);

SQL> insert into emp_new_78 (eno,ename,sal) select eno,ename,sal from emp;

SQL> Select * from emp_new_78

Points to remember: 1. Data types of columns should be matched
2. Number columns should be same
3. Width of column in which we inserting should be equal or morethan the width of source table columns

UPDATE : It is used to replace the existing value with a new value in a column of table.

   Syntax:  sql> UPDATE  TABLE_NAME
                        SET  COLUMN_NAME = NEW_VALUE
               WHERE <CONDITION>;

   Note:  If no where condition is given then it will update for all records.

Examples:

1. SQL> Update purchase
   set price = price + 100;
2. SQL> Update purchase
   set price = null;
3. SQL> update purchase set po_no = null; — it cannot be updated because pono is primary key column
   update purchase set po_no = null
   *
   ERROR at line 1:
   ORA-01407: cannot update (“APPS”.”PURCHASE”.”PO_NO”) to NULL

DELETE : It is used to delete records from the table

Syntax: SQL> Delete from
where

Note: If no where condition is given then it deletes all records from the table

SQL> select * from emp_new_78;

   ENO ENAME             SAL

---

   115 Chandu          28100
   105 Kumar           14200
   111 Ram             14000
   112 Ramesh          25000
   113 Krishna         30000
   114 Shekar          59100

6 rows selected.

SQL> delete from emp_new_78;

6 rows deleted.

SQL> select * from emp_new_78;

no rows selected

SQL> Rollback;

SQL> select * from emp_new_78;

   ENO ENAME             SAL

---

   115 Chandu          28100
   105 Kumar           14200
   111 Ram             14000
   112 Ramesh          25000
   113 Krishna         30000
   114 Shekar          59100

6 rows selected.

TRUNCATE : It is used to remove all records permanently

Syntax : SQL> TRUNCATE table

Points to remember : It deletes all records permanently, and we cannot rollback
No where condition is applicable, hence always it deletes all records

SQL> select * from emp_new_78;

   ENO ENAME             SAL

---

   115 Chandu          28100
   105 Kumar           14200
   111 Ram             14000
   112 Ramesh          25000
   113 Krishna         30000
   114 Shekar          59100

6 rows selected.

SQL> truncate table emp_new_78;

Table truncated.

SQL> select * from emp_new_78;

no rows selected

SQL> rollback;

Rollback complete.

SQL> select * from emp_new_78;

no rows selected

SQL>

Share this post: on Twitter on Facebook on Google+