Data Manipulation Language DML 4 1 Copyright Oracle

Data Manipulation Language (DML) 4 -1 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Data Manipulation Language (DML) • A DML statement is executed when you: – Add

Adding Rows 4 -3 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Adding a New Row to a Table 50 DEVELOPMENT DETROIT New row DEPTNO -----10

The INSERT Statement • Add new rows to a table by using the INSERT

Inserting New Rows • Insert a new row containing values for each column. •

Inserting Rows with Null Values • Implicit method: Omit the column from the column

Inserting Values by Using Substitution Variables Create an interactive script by using SQL*Plus substitution

Copying Rows from Another Table • Write your INSERT statement with a subquery. SQL>

Update Rows 4 -10 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Changing Data in a Table EMPNO ENAME 7839 7698 7782 7566. . . KING

The UPDATE Statement • Modify existing rows with the UPDATE statement. UPDATE SET [WHERE

Updating Rows in a Table • Specific row or rows are modified when you

Updating Rows Based on Another Table Use subqueries in UPDATE statements to update rows

Updating Rows: Integrity Constraint Error SQL> UPDATE 2 SET 3 WHERE emp deptno =

Delete Rows 4 -16 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Removing a Row from a Table DEPTNO -----10 20 30 40 50 60. .

The DELETE Statement You can remove existing rows from a table by using the

Deleting Rows from a Table • Specific row or rows are deleted when you

Deleting Rows Based on Another Table Use subqueries in DELETE statements to remove rows

Deleting Rows: Integrity Constraint Error ry. a im able r p rt a e

Slides: 21

Download presentation

Data Manipulation Language (DML) • A DML statement is executed when you: – Add new rows to a table – Modify existing rows in a table – Remove existing rows from a table • A transaction consists of a collection of DML statements that form a logical unit of work. 4 -2 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Adding a New Row to a Table 50 DEVELOPMENT DETROIT New row DEPTNO -----10 20 30 40 DNAME -----ACCOUNTING RESEARCH SALES OPERATIONS LOC -------NEW YORK DALLAS CHICAGO BOSTON “…insert a new row into DEPT table…” DEPTNO -----10 20 30 40 DNAME -----ACCOUNTING RESEARCH SALES OPERATIONS LOC -------NEW YORK DALLAS CHICAGO BOSTON 50 DEVELOPMENT DETROIT 4 -4 Copyright Ó Oracle Corporation, 1998. All rights reserved.

The INSERT Statement • Add new rows to a table by using the INSERT statement. INSERT INTO table [(column [, column. . . ])] VALUES (value [, value. . . ]); • Only one row is inserted at a time with this syntax. 4 -5 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Inserting New Rows • Insert a new row containing values for each column. • List values in the default order of the columns in the table. • Optionally list the columns in the INSERT clause. SQL> INSERT INTO 2 VALUES 1 row created. dept (deptno, dname, loc) (50, 'DEVELOPMENT', 'DETROIT'); • Enclose character and date values within single quotation marks. 4 -6 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Inserting Rows with Null Values • Implicit method: Omit the column from the column list. SQL> INSERT INTO 2 VALUES 1 row created. dept (deptno, dname ) (60, 'MIS'); • Explicit method: Specify the NULL keyword. SQL> INSERT INTO 2 VALUES 1 row created. 4 -7 dept (70, 'FINANCE', NULL); Copyright Ó Oracle Corporation, 1998. All rights reserved.

Inserting Values by Using Substitution Variables Create an interactive script by using SQL*Plus substitution parameters. SQL> INSERT INTO 2 VALUES 3 dept (deptno, dname, loc) (&department_id, '&department_name', '&location'); Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created. 4 -8 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Copying Rows from Another Table • Write your INSERT statement with a subquery. SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp 4 WHERE job = 'MANAGER'; 3 rows created. • Do not use the VALUES clause. • Match the number of columns in the INSERT clause to those in the subquery. 4 -9 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Changing Data in a Table EMPNO ENAME 7839 7698 7782 7566. . . KING BLAKE CLARK JONES JOB . . . DEPTNO PRESIDENT MANAGER 10 30 10 20 “…update a row in EMP table…” EMPNO ENAME 7839 7698 7782 7566. . . 4 -11 KING BLAKE CLARK JONES JOB PRESIDENT MANAGER Copyright Ó Oracle Corporation, 1998. All rights reserved. . DEPTNO 10 30 20 10 20

The UPDATE Statement • Modify existing rows with the UPDATE statement. UPDATE SET [WHERE table column = value [, column = value] condition]; • Update more than one row at a time, if required. 4 -12 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Updating Rows in a Table • Specific row or rows are modified when you specify the WHERE clause. SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated. • All rows in the table are modified if you omit the WHERE clause. SQL> UPDATE employee 2 SET deptno = 20; 14 rows updated. 4 -13 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Updating Rows Based on Another Table Use subqueries in UPDATE statements to update rows in a table based on values from another table. SQL> UPDATE employee 2 SET deptno = 3 FROM 4 WHERE 5 WHERE job = 6 FROM 7 WHERE 2 rows updated. 4 -14 (SELECT deptno empno = 7788) (SELECT job empno = 7788); Copyright Ó Oracle Corporation, 1998. All rights reserved.

Updating Rows: Integrity Constraint Error SQL> UPDATE 2 SET 3 WHERE emp deptno = 55 deptno = 10; 5 5 r e b m nu t o n s e o d t s i ex UPDATE emp * t n ERROR at line 1: e tm r ORA-02291: integrity constraint (USR. EMP_DEPTNO_FK) a p violated - eparent key not found D 4 -15 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Removing a Row from a Table DEPTNO -----10 20 30 40 50 60. . . 4 -17 DNAME -----ACCOUNTING RESEARCH SALES OPERATIONS LOC -------NEW YORK DALLAS CHICAGO BOSTON DEVELOPMENT DETROIT MIS “…delete a row from DEPT table…” DEPTNO -----10 20 30 40 60. . . DNAME -----ACCOUNTING RESEARCH SALES OPERATIONS MIS Copyright Ó Oracle Corporation, 1998. All rights reserved. LOC -------NEW YORK DALLAS CHICAGO BOSTON

Deleting Rows from a Table • Specific row or rows are deleted when you specify the WHERE clause. SQL> DELETE FROM 2 WHERE 1 row deleted. department dname = 'DEVELOPMENT'; • All rows in the table are deleted if you omit the WHERE clause. SQL> DELETE FROM 4 rows deleted. 4 -19 department; Copyright Ó Oracle Corporation, 1998. All rights reserved.

Deleting Rows Based on Another Table Use subqueries in DELETE statements to remove rows from a table based on values from another table. SQL> DELETE FROM employee 2 WHERE deptno = 3 (SELECT 4 FROM 5 WHERE 6 rows deleted. 4 -20 deptno dept dname ='SALES'); Copyright Ó Oracle Corporation, 1998. All rights reserved.

Deleting Rows: Integrity Constraint Error ry. a im able r p rt a e s SQL> DELETE FROM dept h n t ai no 2 WHERE deptno = 10; t n a o n c yi t a th n ke DELETE FROM dept ow eig r a for * e ERROR at line 1: let s a e constraint a d ORA-02292: integrity (USR. EMP_DEPTNO_FK) d t e o s violated - child found n urecord n a is c u hat o Y yt ke 4 -21 Copyright Ó Oracle Corporation, 1998. All rights reserved.