Skip to main content

Data Manipulation Language(DML)

Data Manipulation Language(DML)

  • A DML statement is executed when you perform following operation

— Inserting data into table
— Updating data into table
— Deleting data from table

  • A transaction consists of a collection of DML statements that form a logical unit of work.

Insert Statement

1. Inserting data into table:
syntax:

INSERT INTO table [(column [, column...])]
VALUES(value [, value...]);

Note: You can insert only one row at a time.

i.e. Inserting data into each and Every column

INSERT INTO departments(department_id,
department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);

i.e. Inserting data into specific column

INSERT INTO departments(department_id,department_name)
                VALUES (110, 'IT');

i.e Inserting data without specifying column name

INSERT INTO departments
VALUES(80, 'Finance', 112, 1636);

i.e. Inserting data using Another table

INSERT INTO EMP1(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct FROM employees;

Update Statement

  • It is used to modify the existing rows in a table.

syntax:

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

If you will specify where clause then Values for a specific row or rows are modified.

i.e. Modifying department id of employee who is having employees_id 100

UPDATE employees
SET department_id = 80
WHERE employee_id = 100

i.e Setting department_id 80 for All employees

UPDATE EMP1
SET department_id = 80;

i.e.Updating two columns with a sub query

UPDATE EMPLOYEES
SET job_id = (SELECT job_id FROM employees WHERE employee_id = 100),
    salary = (SELECT salary FROM employees WHERE employee_id = 100)
WHERE employee_id = 113;

i.e Updating table based on another table

UPDATE EMP1
SET department_id = (SELECT department_id FROM employees 
WHERE employee_id = 110)
WHERE job_id = (SELECT job_id FROM employees
 WHERE employee_id = 100);

Delete Statement

It is used to remove one or entire rows from the table.
Syntax :

 DELETE FROM table
 [WHERE condition];

Example :

DELETE FROM EMPLOYEES
WHERE EMP_Name = ‘SAM';

Note : Above query deletes whole record if you will not use where clause.