Data Manipulation Language (DML)-:Data Manipulation Language (DML) is a core part of SQL. When we want to add, update or delete data in the database, we execute a DML statement. A collection of DML statements that form a logical unit of work is called transaction.
DML Statements :
| Statement | Purpose |
| Insert | Add rows to table |
| Update | Change the values in table |
| Delete | Remove rows from a table |
The INSERT Statement :-We can add new rows to a table by using the INSERT statement.
INSERT INTO table[(column [,column…])] VALUES (value [,value…] );
In the syntax -:Table is the name of the table.
Column is the name of the column in the tablet to populate.
Value is the corresponding value for the column.
- This statement with the VALUES clause adds only one row at a time to a table.
- Insert a new row containing values for each column.
- List values in the default under of the columns in the table.
- Optionally list the columns in the INSERT clause.
- Enclose character and date values within single quotation marks.
INSERT INTO depot (deptno, dname, loc)
VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’);
Because we can insert a new row that contains values for each column, the column list is not required in the INSERT clause . However, if we do not use the column list, the values must be listed according to the default order of the columns in the table.
SQL>DESCRIBE dept;
Name Null? Type
————— ——————– ——————–
DEPTNO NOT NULL NUMBER (2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
For clarity, use the column list in the INSERT clause.
Inserting Rows with NULL Values:-
| Method | Description |
| Implicit | Omit the column from the column list. |
| Explicit | Specify the NULL keyword in the VALUES list. |
| Specify the empty string(‘ ‘) in the VALUES list; for character strings and date only. |
The Server automatically enforces all datatypes, data ranges, and data integrity constraints. Any column that is not listed explicitly obtains a null value in the new row.
INSERT INTO dept (deptno,dname) VALUES ( 60,’MIS’);
INSERT INTO dept VALUES (70,’FINANCE’,NULL);
Common error that can occur during user input:
- Mandatory value missing for a NOT NULL column.
- Duplicate value violates uniqueness constraint.
- Foreign key constraint violated.
- Datatype mismatch.
- Value too wide to fit in column.
Inserting Special Values by Using SQL Functions:-
We can use pseudocolumns to enter special values in we tables. INSERT INTO emp(empno,dname) VALUE (7196,’GREEN’,’SALESMAN’,’7782,SYSDATE,2000,NULL,10);
The example records information for employee Green in the EMP table. It supplies the current date and time in the HIREDATE column. It uses the SYSDATE function for current date and time.
We can use the USER function when inserting rows in a table. The USER function records the current username.
Inserting Specific Date Values:-The format DD-MM-YY is usually used to insert a date value. Because the date also contains time information, the default time is midnight (00:00:00). If a date must be entered in a format other than the default format – for example, another century and/or a specific time- we must use the TO_DATE function.
INSERT INTO emp VALUES (2296,’AROMANO’,’SALESMAN’,’7782,TO_DATE(‘FEB 3,1997’,’MM,DD,YYYY’)1300,NULL,10);
Inserting Values by Using Substitution Variables:-We can produce an INSERT statement that allows the user to add values interactively by using SQL *Plus substitution variables.
INSERT INTO dept (deptno, dname, loc) VALUES (&department_id,’&department_name’,’&location’);
SQL>Enter value for department_id:80
SQL>Enter value for department_name: EDUCATION
SQL>Enter value for location: ATLANTA
The example records information for a department in the DEPT table. It prompts the user for the department number, department name, and location. For date and character values, the ampersand and the variable name are enclosed in single quotation marks.
Copying Rows from Another Table:-We can use the INSERT statement to add rows to a table where the values are derived from existing tables. In place of the VALUES clause, we use a subquery.
Syntax :
INSERT INTO table (column, [,column ])
Subquery;
Where :
Table is the table name
Column is the name of the column in the table to populate.
Subquery is the subquery that returns tows into the table.
The number of columns and their datatypes in the column list of the INSERT clause match the number of values and their datatypes in the subquery.
INSERT INTO managers (id,name,salary,hiredate)
SELECT empno,ename,sal,hiredate
FROM emp
WHERE job = ‘MANAGER’;
The UPDATE Statement:-We can modify existing rows by using the UPDATE statement. In the Syntax
UPDATE table SET column = value [,column=value,..] [WHERE condition];
In the syntax
Table is the name of the table
Column is the name of the column in the table to populate
Value is the corresponding value or subquery for the column
Condition identifies the rows to be updated and is composed of column names, expression, constants, subqueries, and comparison operators.
In general, use the primary key to identify a single row. Using other columns may unexpectedly cause several rows to be updated. For example , identifying a single row in the EMP table by name is dangerous because more than one employee may have the same name.
UPDATE emp SET deptno=20 WHERE empno=7782;
1 row updated.
The UPDATE statement modifies specific rows, if the WHERE clause is specified. The example transfers employee 7782 (Clark) to department 20. If you omit the WHERE clause, at the rows in the table are modified.
UPDATE emp SET deptno=20; 14 rows updated
Updating Rows with a Multiple Column Subquery:-Multiple column subqueries can be implemented in the SET clause of an UPDATE statement. In the Syntax:
UPDATE table SET(column, column,…)=(SELECT column,column,…)FROM table WHERE condition) ;
UPDATE emp SET (job, deptno)=(SELECT job, deptno FROM emp WHERE empno=7499) WHERE empno=7698;
1 row updated
Updating Rows
Integrity constraint Error : If we attempt to update a record with a value that is tied to an integrity constraint, we will experience an error.
UPDATE emp SET deptno = 55 WHERE deptno=10;
UPDATE emp * ERROR at line 1;
ORA – 02291: integrity constraint (USR.EMP_DEPTNO_FK)
Violated –parent key not found.
In the example department number 55 does not exist in the parent table DEPT, and so we receive the parent key violation. Integrity constraints ensure that the data adheres to a predefined set of rules.
The Delete Statement:We can remove existing rows from a table by using the DELETE statement.
Syntax
DELETE [FROM] table [WHERE condition];
Where:
table -: is the name
condition :- identifies the rows to be deleted and is composed of column names, expressions, constants, subqueries, and comparison operators.
We can delete specific rows by specifying the WHERE clause in the DELETE statement.
DELETE FROM department WHERE dname =’DEVELOPMENT’;
1 row deleted
The example deletes the DEVELOPMENT department from the DEPARTMENT tale.
If we omit the WHERE clause, all rows in the table are deleted.
DELETE FROM department;
4 rows deleted
The example deletes all the rows from the DEPARTMENT table because no WHERE clause has been specified.
Deleting Rows Based on Another Table:- We can use subqueries to delete rows from a table based on values form another table.
DELETE FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname= ‘SALES’); 6 rows deleted
The example deletes all the employees who are in department 30. The subquery searches the DEPT table to find the department number for the SALES department. The subquery then feeds the department number to the main query, which deletes rows of data from the EMP table based on this department number.
Integrity Constraint Error:- If we attempt to delete a record with a value that is tied to an integrity constraint, we will experience an error.
DELETE FROM dept WHERE deptno = 10;
DELETE FROM dept * ERROR at line 1 : ORA-02292:integrity constraint (USER.EMP_DEPTNO_FK) Violated – child record found.
The example tries to delete department number 10 from the DEPT table, but it results in an error because department number is used as a foreign key in the EMP table. If the parent record that we attempt to delete has child records, then we receive the child record found.
Explicit Default Feature:-With the explicit default feature, we can use the DEFAULT keyword as a column value where the column default is desired.This allows the user to control where and when the default value should be applied to data, Explicit defaults can be used in INSERT and UPDATE statements.
DEFAULT with INSERT INSERT INTO dept (deptno,dname,loc) VALUES(60,’PRODUCTION,’DEFAULT);
In the above mentioned example,the INSERT statement uses a default value for the LOC column. If there is no default value defined for the column, a null value is inserted instead.
DEFAULT with UPDATE SET deptno = DEFAULT WHERE deptno=60;
In the above mentioned example the UPDATE statement to set LOC column to default for department 60.If no default value is defined for the column, it changes the value to null.
The Merge Statement:-SQL has been extended to include the MERGE statement. Using this statement, we can update or insert a row conditionally into a table, thus avoiding multiple UPDATE statements. The decision whether to update or insert into the target table is based on a condition in the ON clause. Since the MERGE command combines the INSERT and UPDATE commands, we need both INSERT and UPDATE privileges on the target table and the SELECT privilege on the source table.
The MERGE statement is deterministic. We cannot update the same row of the target table multiple times in the same MERGE statement. Therefore MERGE statement
1. Avoids separate updates.
2. Increases performance and ease of use.
3. Is useful in data warehousing applications.
Syntax
MERGE INTO table_name table_alias
USING( table|view|sub_query)alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
Col1 =coll_val1,
Col2 = col2_val2
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
In the syntax
INTO clause specifies the target table we are updating or inserting into.
USING clause identifies the source of the data to be updated or inserted; on table,
view or subquery.
ON clause the condition upon which the MERGE operation either updates or inserts
WHEN MATCHED | instructs the server how to respond to the results of the join condition
WHEN NOT MATCHED
MERGE INTO copy_emp c
USING employee e
ON (c.empid =e.empid)
WHEN MATCHED THEN
UPDATE SET
c.ename =e.name,
c.job = e.job
….
c.deptno = e.deptno
WHEN NOT MATCHED THEN
INSERT VALUES
(e.empid, e.ename, e.hiredate, ejob, e.sal, e.comm., e.mgr, e.deptno);
The example shown maches the EMPID in the COPY_EMP table to the EMPID in the EMP table. If match is found, the row in the COPY_EMP table is updated to match the row in the EMP table. If the row is not found, it is inserted into the COPY_EMP table.
The condition c.empid = e.empid is evaluated, because the COPY_EMP is empty, the condition returns false: there are no matches. The logic falls into the WHEN NO MATCHED clause, and the MERGE command inserts the rows of the EMP table into COPY_EMP table. If rows existed in the COPY_EMP table and EMPIDs matced in both tables ( the COPY_EMP , EMP tables),the existing rows in the COPY_EMP table would be updated to match EMP table.