Understanding DB2: Learning Visually with Examples (2nd Edition)

9.2. Modifying DB2 Data

You modify DB2 data using the INSERT, UPDATE, and DELETE statements. Most of the clauses and functions described in the previous section also work with these statements. We will use some examples to explain their basic usages.

You can specify all the column values in the INSERT statement like this:

INSERT INTO employee VALUES ( '000998', 'SMITH', 'A', 'JOHN', NULL, NULL, NULL, NULL, 18, 'M', NULL, NULL, NULL, NULL ) ;

Alternatively, you can explicitly specify the column list for which values will be provided in the INSERT statement:

INSERT INTO employee (empno, firstnme, midinit, lastname, edlevel) VALUES ( '000999', 'SMITH', 'A', 'JOHN', 18 );

You can also insert multiple rows in one INSERT statement:

INSERT INTO employee (empno, firstnme, midinit, lastname, edlevel) VALUES ( '000999', 'SMITH', 'A', 'JOHN', 18 ) , ( '000998', 'LOPEZ', 'M', 'JEN' , 18 ) , ( '000997', 'FRASER', 'B', 'MARC', 28 );

A multi-row insert is achieved with values obtained from a SELECT statement:

INSERT INTO employee_temp ( SELECT * FROM employee );

It is fairly straightforward to update one or more rows in a table by simply assigning the new values in the SET clause:

UPDATE employee SET salary = salary * 1.5, comm = 0 WHERE empno='000999';

This next UPDATE statement obtains the department number from the department table. Note that the DB2 special register CURRENT DATE is used as the value to the hire date.

UPDATE employee SET (hiredate, workdept) = (SELECT CURRENT DATE, deptno FROM department WHERE deptname='PLANNING') WHERE empno='000999';

The DELETE statement is used to delete rows from a table. To remove all rows from the employee table, use the following statement:

DELETE FROM employee;

To remove only certain rows, use the WHERE clause to filter the rows:

DELETE FROM employee WHERE workdept IS NULL;

To remove rows with a row number greater than 100, use the ROWNUMBER()OVER() functions like this:

DELETE FROM (SELECT ROWNUMBER() OVER(ORDER BY empno) AS rowid FROM employee) WHERE rowid > 100