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 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;
- It is used to modify the existing rows in a table.
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 FROM table [WHERE condition];
DELETE FROM EMPLOYEES WHERE EMP_Name = ‘SAM';
Note : Above query deletes whole record if you will not use where clause.