Learning Visually with Examples [Electronic resources] نسخه متنی

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