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