DB2 INSERT, UPDATE, DELETE: Complete DML Guide with Examples

Introduction: Mastering DB2 Data Manipulation Language

Reading data with SELECT is only half the story. In any real DB2 application — whether it's a mainframe CICS transaction, a batch COBOL job, or a Java microservice — you also need to write data. The three core Data Manipulation Language (DML) statements in DB2 are INSERT, UPDATE, and DELETE, joined by the powerful MERGE statement that combines all three into a single operation. Understanding how to use these statements correctly — and safely — is essential for anyone working with IBM DB2.

This guide covers every major form of DB2 INSERT, UPDATE, and DELETE, with practical, tested SQL examples using the IBM sample tables (EMPLOYEE, DEPARTMENT, PROJECT). It also covers the MERGE statement for upsert patterns, TRUNCATE for bulk removal, and the critical topic of transaction control with COMMIT and ROLLBACK. Let's work through each statement methodically.

DB2 INSERT Statement

Single-Row INSERT

The most basic form of INSERT adds one row to a table. You specify the target table, optionally list the columns you are inserting into (strongly recommended), and then provide the VALUES.

-- Insert a single new employee record
INSERT INTO EMP.EMPLOYEE
       (EMPNO, FIRSTNME, MIDINIT, LASTNAME,
        WORKDEPT, PHONENO, HIREDATE,
        JOB, EDLEVEL, SEX, BIRTHDATE,
        SALARY, BONUS, COMM)
VALUES ('E999', 'JANE', 'A', 'SMITH',
        'A00', '1234', DATE('2026-04-20'),
        'ANALYST', 18, 'F', DATE('1990-06-15'),
        65000.00, 1000.00, 500.00);

Always list column names explicitly. If you omit them and rely on positional VALUES, any future table alteration that adds or reorders columns will silently insert data into the wrong columns — a category of bug that is extremely difficult to detect in production.

INSERT with NULL Values

For nullable columns where you have no value, either omit the column from the column list (it will default to NULL) or pass NULL explicitly:

-- Insert an employee with no bonus or commission yet assigned
INSERT INTO EMP.EMPLOYEE
       (EMPNO, FIRSTNME, MIDINIT, LASTNAME,
        WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX)
VALUES ('E888', 'MARK', 'B', 'JONES',
        'B01', '5678', CURRENT DATE,
        'DESIGNER', 16, 'M');
-- SALARY, BONUS, COMM, BIRTHDATE default to NULL

Multi-Row INSERT

DB2 for LUW and DB2 for z/OS version 11 and later support inserting multiple rows in one statement using the row-value constructor:

-- Insert two department records in a single statement
INSERT INTO EMP.DEPARTMENT
       (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
VALUES ('Z01', 'AI RESEARCH',   'E001', 'A00'),
       ('Z02', 'DATA PLATFORM', 'E002', 'A00');

This is more efficient than two separate INSERT statements because it reduces the number of network round-trips and can be optimized into a single log write in some configurations.

INSERT from SELECT (INSERT ... SELECT)

The most powerful and scalable INSERT pattern copies rows from one or more source tables directly into a target table. This avoids fetching data into the application layer before reinserting it.

-- Archive employees hired before 2010 into a history table
INSERT INTO EMP.EMPLOYEE_HISTORY
       (EMPNO, FIRSTNME, LASTNAME, WORKDEPT,
        HIREDATE, SALARY, ARCHIVE_DATE)
SELECT EMPNO,
       FIRSTNME,
       LASTNAME,
       WORKDEPT,
       HIREDATE,
       SALARY,
       CURRENT DATE
FROM   EMP.EMPLOYEE
WHERE  HIREDATE < DATE('2010-01-01');

The SELECT portion of an INSERT...SELECT can be as complex as any standalone query — with JOINs, GROUP BY, subqueries, and expressions — as long as the result set structure matches the target columns.

DB2 UPDATE Statement

Basic UPDATE with WHERE

The UPDATE statement modifies existing rows. The WHERE clause identifies which rows to change. Without a WHERE clause, every row in the table is updated — always double-check with a SELECT before executing.

-- Give employee E999 a salary raise and update their department
UPDATE EMP.EMPLOYEE
SET    SALARY   = 72000.00,
       WORKDEPT = 'B01'
WHERE  EMPNO = 'E999';

You can update multiple columns in a single SET clause, which is more efficient than running separate UPDATE statements for each column.

-- Apply a 5% cost-of-living raise to all analysts in department C01
UPDATE EMP.EMPLOYEE
SET    SALARY = SALARY * 1.05,
       BONUS  = BONUS  + 500.00
WHERE  JOB      = 'ANALYST'
  AND  WORKDEPT = 'C01';

UPDATE with a Subquery

When the new value depends on data from another table, embed a scalar subquery in the SET clause:

-- Set each employee's department name in a denormalised table
-- based on the current DEPARTMENT table values
UPDATE EMP.EMPLOYEE_DENORM E
SET    DEPTNAME = (
         SELECT D.DEPTNAME
         FROM   EMP.DEPARTMENT D
         WHERE  D.DEPTNO = E.WORKDEPT
       )
WHERE EXISTS (
         SELECT 1
         FROM   EMP.DEPARTMENT D2
         WHERE  D2.DEPTNO = E.WORKDEPT
       );

The WHERE EXISTS guard prevents the UPDATE from setting DEPTNAME to NULL for employees whose WORKDEPT has no matching row in DEPARTMENT.

UPDATE Using MERGE (SET with JOIN Logic)

DB2 does not support a direct UPDATE ... FROM ... JOIN syntax (unlike SQL Server). The equivalent in DB2 is achieved either with correlated subqueries (as shown above) or with the MERGE statement. See the MERGE section below for the full pattern.

DB2 DELETE Statement

Basic DELETE with WHERE

DELETE removes rows that satisfy the WHERE condition. Like UPDATE, a DELETE without WHERE removes every row in the table.

-- Remove a specific employee record
DELETE FROM EMP.EMPLOYEE
WHERE  EMPNO = 'E999';

DELETE with Multiple Conditions

-- Remove all temporary contractors hired before 2020 with low salary
DELETE FROM EMP.EMPLOYEE
WHERE  JOB     = 'CONTRACTOR'
  AND  HIREDATE < DATE('2020-01-01')
  AND  SALARY   < 40000.00;

DELETE with a Subquery

When the delete criteria involve data from another table, use a correlated subquery or a subquery in the WHERE clause:

-- Delete all employees who belong to departments that have been closed
DELETE FROM EMP.EMPLOYEE
WHERE  WORKDEPT IN (
         SELECT DEPTNO
         FROM   EMP.DEPARTMENT
         WHERE  STATUS = 'CLOSED'
       );

Counting Affected Rows

After a DELETE (or UPDATE or INSERT), you can check how many rows were affected using the SQLERRD fields in the SQLCA (for embedded SQL) or the getUpdateCount() method in JDBC. In SPUFI or CLPPlus, the row count is shown in the result summary.

TRUNCATE vs DELETE: Which Should You Use?

Both TRUNCATE and DELETE without a WHERE clause remove all rows from a table, but they behave very differently:

-- Remove all rows slowly but safely (rollback-able on all platforms)
DELETE FROM EMP.EMPLOYEE_STAGING;

-- Remove all rows instantly with minimal logging (use with caution)
TRUNCATE TABLE EMP.EMPLOYEE_STAGING IMMEDIATE;

On DB2 for z/OS, TRUNCATE TABLE issues an implicit COMMIT before and after execution, so there is no way to roll it back. On DB2 LUW, TRUNCATE is transactional and can be rolled back. Know your platform before using TRUNCATE in a production script.

The DB2 MERGE Statement (Upsert)

MERGE is one of DB2's most sophisticated DML statements. It compares a source dataset against a target table and applies INSERT, UPDATE, or DELETE operations depending on whether source rows match target rows. This is the standard DB2 pattern for an "upsert" — insert if new, update if existing.

-- Upsert employee data from a staging table into the main EMPLOYEE table
MERGE INTO EMP.EMPLOYEE AS TGT
USING (
    SELECT EMPNO, FIRSTNME, LASTNAME,
           WORKDEPT, SALARY, HIREDATE
    FROM   EMP.EMPLOYEE_STAGING
) AS SRC
ON (TGT.EMPNO = SRC.EMPNO)

WHEN MATCHED THEN
    UPDATE SET
        TGT.FIRSTNME  = SRC.FIRSTNME,
        TGT.LASTNAME  = SRC.LASTNAME,
        TGT.WORKDEPT  = SRC.WORKDEPT,
        TGT.SALARY    = SRC.SALARY

WHEN NOT MATCHED THEN
    INSERT (EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, HIREDATE)
    VALUES (SRC.EMPNO, SRC.FIRSTNME, SRC.LASTNAME,
            SRC.WORKDEPT, SRC.SALARY, SRC.HIREDATE);

You can also add a WHEN NOT MATCHED BY SOURCE THEN DELETE clause to remove target rows that no longer exist in the source — making MERGE a complete sync operation in a single SQL statement.

-- Full sync: update matches, insert new, delete orphans
MERGE INTO EMP.EMPLOYEE AS TGT
USING EMP.EMPLOYEE_STAGING AS SRC
ON (TGT.EMPNO = SRC.EMPNO)

WHEN MATCHED THEN
    UPDATE SET TGT.SALARY = SRC.SALARY,
               TGT.WORKDEPT = SRC.WORKDEPT

WHEN NOT MATCHED THEN
    INSERT (EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, HIREDATE)
    VALUES (SRC.EMPNO, SRC.FIRSTNME, SRC.LASTNAME,
            SRC.WORKDEPT, SRC.SALARY, SRC.HIREDATE)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

MERGE is atomic — it either completes entirely or fails entirely, making it safe for concurrent environments.

Transaction Control: COMMIT and ROLLBACK

All DB2 DML statements execute within a transaction (also called a Unit of Work, or UoW, on z/OS). Changes made by INSERT, UPDATE, DELETE, and MERGE are not visible to other connections and cannot be considered permanent until a COMMIT is issued.

-- Begin a transaction (implicit in DB2 — no BEGIN TRANSACTION needed)

UPDATE EMP.EMPLOYEE
SET    SALARY = SALARY * 1.10
WHERE  WORKDEPT = 'A00';

-- Verify the change before committing
SELECT EMPNO, LASTNAME, SALARY
FROM   EMP.EMPLOYEE
WHERE  WORKDEPT = 'A00';

-- If the results look correct, make them permanent
COMMIT;

-- If something is wrong, undo all changes since the last COMMIT
-- ROLLBACK;

DB2 does not require an explicit BEGIN TRANSACTION statement. Each new connection or post-COMMIT/ROLLBACK point automatically starts a new transaction. Key rules to follow:

Always COMMIT after a logical unit of work is complete. On z/OS CICS, the CICS syncpoint mechanism handles COMMIT/ROLLBACK; do not issue SQL COMMIT directly in CICS-managed programs. For long-running batch jobs, issue periodic COMMITs (every 1,000 or 10,000 rows) to release locks and prevent DB2 log full conditions.

Bulk Operations and Performance Considerations

For inserting or updating large volumes of rows, application-layer looping (one SQL call per row) is extremely inefficient. DB2 provides several bulk-oriented alternatives.

The INSERT...SELECT pattern shown earlier is the most universally supported approach — it pushes all the data movement work to the DB2 engine, which can optimise it internally.

For DB2 LUW, the LOAD utility inserts data from an external file at very high speed with minimal logging. On z/OS, the equivalent is the DSNUTILB LOAD utility, which bypasses normal SQL processing to achieve rates of millions of rows per minute.

For UPDATE and DELETE operations affecting a significant portion of a table (more than 5-10% of rows as a rough guideline), it is often faster to use INSERT...SELECT into a new table, rename/swap the tables, and rebuild indexes — rather than running a massive UPDATE that locks the table for extended periods.

Common DML Errors and How to Fix Them

SQLCODE -803: Duplicate key. You are inserting a row whose primary key or unique index value already exists. Fix: check for existence with SELECT before INSERT, or use MERGE with the WHEN MATCHED/WHEN NOT MATCHED logic.

SQLCODE -530: Foreign key violation. You are inserting a child row (e.g., an EMPLOYEE with a WORKDEPT) that has no matching parent row in DEPARTMENT. Fix: ensure the parent record exists first, or insert both within the same transaction.

SQLCODE -532: Cannot delete parent row. You are deleting a DEPARTMENT row that still has child EMPLOYEE rows referencing it. Fix: delete the children first, or define the foreign key with ON DELETE CASCADE.

SQLCODE -904: Resource unavailable. Another transaction holds a lock on the rows you are trying to update or delete. Fix: check for long-running uncommitted transactions using DB2 monitoring tools, and ensure your own transactions COMMIT promptly.

Mass update/delete without WHERE. Always run the equivalent SELECT with the same WHERE clause first to see what will be affected. In SPUFI, preview with SELECT COUNT(*) before committing to a destructive DML statement.

Putting It All Together: A Complete DML Workflow

Here is a realistic end-to-end example combining all four DML operations in a payroll adjustment workflow:

-- Step 1: Load new salary data into staging
INSERT INTO EMP.SALARY_STAGING
       (EMPNO, NEW_SALARY, EFFECTIVE_DATE)
VALUES ('E001', 95000.00, CURRENT DATE),
       ('E002', 72000.00, CURRENT DATE),
       ('E003', 61000.00, CURRENT DATE);

-- Step 2: Apply raises using MERGE
MERGE INTO EMP.EMPLOYEE AS TGT
USING EMP.SALARY_STAGING AS SRC
ON (TGT.EMPNO = SRC.EMPNO)
WHEN MATCHED AND SRC.NEW_SALARY > TGT.SALARY THEN
    UPDATE SET TGT.SALARY = SRC.NEW_SALARY;

-- Step 3: Archive departed employees
INSERT INTO EMP.EMPLOYEE_HISTORY
SELECT *, CURRENT DATE AS ARCHIVE_DATE
FROM   EMP.EMPLOYEE
WHERE  TERMDATE IS NOT NULL
  AND  TERMDATE < CURRENT DATE;

DELETE FROM EMP.EMPLOYEE
WHERE  TERMDATE IS NOT NULL
  AND  TERMDATE < CURRENT DATE;

-- Step 4: Clean up staging table
TRUNCATE TABLE EMP.SALARY_STAGING IMMEDIATE;

-- Step 5: Commit the entire unit of work
COMMIT;

This workflow moves through INSERT, MERGE, INSERT...SELECT, DELETE, TRUNCATE, and COMMIT in a logical sequence — the kind of multi-step transaction you encounter routinely in mainframe batch programs.

Key Takeaways

DB2's DML statements — INSERT, UPDATE, DELETE, and MERGE — give you precise, transactional control over your data. The key discipline is always writing your WHERE clause first (tested with SELECT), committing at sensible boundaries, and choosing the right tool for scale (INSERT...SELECT and MERGE over row-by-row application loops). Understanding TRUNCATE versus DELETE, and knowing when MERGE eliminates redundant round-trips, elevates your SQL from functional to production-quality.

For a quick syntax reference on all DB2 SQL statements, see the DB2 Cheat Sheet. To progress through DB2 JOINs, indexing, and query optimisation, explore the DB2 Mastery Course.