Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI Data Manipulation Language Insert, Updates, Deletes Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Data Manipulation Language Statements • Insert • Rollback • Commit • Update • Merge • Delete • Transaction Management allows for read-consistent views. • Data is but published to other users until it is committed. • Allow uncommitted transactions to be rolled back. 2

3. Insert • Allows you to insert a row or rows into tables or views. • Syntax1: INSERT INTO tablename / viewname (column1, column2, ... ) VALUES (value1, value2, ... ); • Syntax2: INSERT INTO tablename / viewname (column1, column2, ... ) subquery; • Column names are optional, but then the values must be in order of the columns. 3

4. Insert Examples • The following statement inserts a row into the DEPT table: INSERT INTO dept VALUES (50, ’PRODUCTION’, ’SAN FRANCISCO’); • The following statement inserts a row with six columns into the EMP table. One of these columns is assigned NULL and another is assigned a number in scientific notation: INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (7890, ’JINKS’, ’CLERK’, 1.2E3, NULL, 40); • The following statement copies managers and presidents or employees whose commission exceeds 25% of their salary into the BONUS table:INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN (’PRESIDENT’, ’MANAGER’); 4

5. Insert with Append Hint • To improve insert performance, especially for large subquery inserts, you can use the /*+ APPEND */ hint. Insert /*+ APPEND */ into mytable (col1) select mycol from othertable; • Normally insert looks for freespace inside currently allocated blocks on disk. • Append overrides this behavior and appends data to the table by allocating new data blocks. • Hints are non-ANSI standard. This only works in Oracle. 5

6. Rollback, Commit, Autocommit • Rollback allow you to reverse changes. • Commit make your changes permanent. 6

7. Transactions, Savepoint • Transactions begin when: • a SQL satatement is first issued, • immediately after a commit, • immediately after a rollback. • Commits occur when: • Commit (explicit) • quit/exit a program (implicit) • DDL (implicit) • Rollbacks occur when: • Rollback (explicit) • Abnormal program termination (implicit) • Database crash (implicit) • SAVEPOINT allows you to rollback to a specific point in a transaction. Example:UPDATE empSET sal = 2000WHERE ename = ’BLAKE’;SAVEPOINT blake_salUPDATE empSET sal = 1500WHERE ename = ’CLARK’;SAVEPOINT clark_salSELECT SUM(sal) FROM emp;ROLLBACK TO SAVEPOINT blake_sal;UPDATE empSET sal = 1300WHERE ename = ’CLARK’;COMMIT; 7

8. The following statement deletes all rows from a table named TEMP_ASSIGN.DELETE FROM temp_assign; The following statement deletes from the employee table all sales staff who made less than $100 commission last month:DELETE FROM empWHERE JOB = ’SALESMAN’ AND COMM < 100; The following statement gives null commissions to all employees with the job TRAINEE:UPDATE emp SET comm = NULL WHERE job = ’TRAINEE’; The following statement promotes JONES to manager of Department 20 with a $1,000 raise:UPDATE emp SET job = ’MANAGER’, sal = sal + 1000, deptno = 20 WHERE ename = ’JONES’; Delete, Update Examples 8

9. More complex example with multiple subqueries.UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = ’BOSTON’), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ’DALLAS’ OR loc = ’DETROIT’); Update Example 9

10. Multi-table inserts are a new 9i feature. WHEN clause indicates which VALUES clause to use. ALL or FIRST indicates to evaluate all or only the first WHEN/VALUE clause per row. Insert ALL|FIRSTwhen (condition1) theninsert clause1 when (condition2) theninsert clause2 when (condition3) theninsert clause3 else insert clause4 Subquery; Multi-table Inserts 10

11. INSERT ALL Example INSERT ALL INTO BOOKSHELF(TITLE, PUBLISHER, CATEGORYNAME, RATING) VALUES(TITLE, PUBLISHER, CATEGORYNAME, 5) INTO BOOKSHELF(TITLE, PUBLISHER, CATEGORYNAME, RATING) VALUES(TITLE || '-1', PUBLISHER, CATEGORYNAME, 4) INTO BOOKSHELF(TITLE, PUBLISHER, CATEGORYNAME, RATING) VALUES(TITLE || '-2', PUBLISHER, CATEGORYNAME, 3) SELECT 'MY FIRST BOOK' TITLE, 'RANDOM PRESS' PUBLISHER, 'ADULTFIC' CATEGORYNAME FROM DUAL;

12. Merge is a new 9i feature, commonly called UPSERT. Combines the features of an INSERT with UPDATE. Based on the conditions you specify in the WHEN clause, Oracle takes the source data (table, view, subquery) and updates the existing rows. If the conditions are not met, a new row is inserted. MERGE into COMFORT C1 USING (select City, SampleDate, Noon from COMFORT) C2 ON (C1.City=C2.City and C1.SampleDate=C2.SampleDate) WHEN MATCHED THEN update set C1.Noon = C2.Noon WHEN NOT MATCHED THEN insert (C1.City, C1.SampleDate, C1.Noon) values (C2.City, C2.SampleDate, C2.Noon); Merge

13. Acknowledgements • Loney, Oracle Database 10g The Complete Reference