CRUD Operations

Difficulty: Beginner · ~10 min read

Overview

CRUD stands for Create, Read, Update, Delete — the four basic operations every database supports:

Operation	SQL keyword	Purpose
Create	INSERT	Add new rows
Read	SELECT	Query existing rows
Update	UPDATE	Modify existing rows
Delete	DELETE	Remove rows

DB2 also supports a fifth, very useful operation: MERGE, which combines insert + update ("upsert") in one statement.

Syntax

-- INSERT
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- SELECT
SELECT col1, col2 FROM table_name WHERE condition FETCH FIRST n ROWS ONLY;

-- UPDATE
UPDATE table_name SET col1 = val1 WHERE condition;

-- DELETE
DELETE FROM table_name WHERE condition;

-- MERGE (upsert)
MERGE INTO target USING source ON match_condition
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

Examples

We'll use a products table throughout:

CREATE TABLE products (
  product_id  INTEGER       NOT NULL PRIMARY KEY,
  name        VARCHAR(80)   NOT NULL,
  category    VARCHAR(40),
  price       DECIMAL(9,2)  NOT NULL,
  stock       INTEGER       NOT NULL DEFAULT 0
);

Example 1: INSERT — single, multi-row, and from SELECT

-- Single row
INSERT INTO products VALUES (1, 'Widget', 'Hardware', 9.99, 100);

-- Multiple rows in one statement
INSERT INTO products (product_id, name, category, price, stock) VALUES
  (2, 'Gadget',  'Hardware',  14.50, 50),
  (3, 'Sprocket','Hardware',   3.25, 200),
  (4, 'Manual',  'Books',     19.99, 25);

-- Insert from another query
INSERT INTO products_archive (product_id, name, price)
  SELECT product_id, name, price
  FROM   products
  WHERE  stock = 0;

Example 2: SELECT — the basics + DB2-specific pagination

-- All columns
SELECT * FROM products;

-- Specific columns with filtering and ordering
SELECT name, price
FROM   products
WHERE  category = 'Hardware'
  AND  price < 10
ORDER  BY price DESC;

-- DB2 pagination (top 2 most expensive)
SELECT name, price
FROM   products
ORDER  BY price DESC
FETCH FIRST 2 ROWS ONLY;

Output:

NAME      PRICE
--------  ------
Manual    19.99
Gadget    14.50

DB2 uses FETCH FIRST n ROWS ONLY (standard SQL), not LIMIT (MySQL/Postgres) or TOP n (SQL Server).

Example 3: UPDATE — single and conditional

-- Bump all hardware prices by 10%
UPDATE products
SET    price = price * 1.10
WHERE  category = 'Hardware';

-- Set stock to 0 for a specific product
UPDATE products
SET    stock = 0
WHERE  product_id = 3;

-- Update multiple columns at once
UPDATE products
SET    (price, stock) = (29.99, 10)
WHERE  product_id = 4;

Example 4: DELETE

-- Delete a single row
DELETE FROM products WHERE product_id = 3;

-- Delete with a subquery
DELETE FROM products
WHERE  category IN (SELECT category FROM discontinued_categories);

-- Delete everything (use with extreme caution — no rollback after COMMIT!)
DELETE FROM products;

Example 5: MERGE — upsert in one shot

MERGE INTO products AS p
USING (VALUES (1, 'Widget v2', 'Hardware', 12.00, 75)) AS s(id, name, cat, price, stock)
  ON p.product_id = s.id
WHEN MATCHED THEN
  UPDATE SET p.name = s.name, p.price = s.price, p.stock = s.stock
WHEN NOT MATCHED THEN
  INSERT (product_id, name, category, price, stock)
  VALUES (s.id, s.name, s.cat, s.price, s.stock);

If product 1 exists → it gets updated. If not → it gets inserted.

Example 6: SELECT with aggregation

SELECT  category,
        COUNT(*)   AS num_items,
        SUM(stock) AS total_stock,
        AVG(price) AS avg_price
FROM    products
GROUP BY category
HAVING  COUNT(*) > 1
ORDER BY category;

Notes & Tips

• Always COMMIT when running statements through the db2 CLP unless you have AUTOCOMMIT ON (the default in many clients). Otherwise your changes will be rolled back when the session ends.
• The FETCH FIRST n ROWS ONLY clause replaces the older OPTIMIZE FOR n ROWS — use FETCH FIRST in new code.
• DB2 supports OFFSET n ROWS FETCH FIRST m ROWS ONLY for paging since v11.1.
• For UPDATE/DELETE without a WHERE, DB2 will touch every row. Get into the habit of writing the WHERE first.
• INSERT … SELECT is the most efficient way to copy data between tables — much faster than row-by-row.
• DB2 supports the RETURNING clause inside a SELECT FROM FINAL TABLE (UPDATE …) — see Example below.

-- Get the updated rows in the same statement
SELECT product_id, price
FROM FINAL TABLE (UPDATE products SET price = price * 1.10 WHERE category = 'Hardware');

Practice Exercises

1. Insert three rows into a customers table you create. Each row should use the CURRENT_TIMESTAMP default for a created_at column.
2. Increase every customer's loyalty_points by 5%, but only for customers whose region = 'EU'.
3. Write a MERGE that inserts a customer if their email is new, or updates their name if the email already exists.
4. Use FETCH FIRST 5 ROWS ONLY to return the 5 most recent customers.
5. Use SELECT … FROM FINAL TABLE (DELETE …) to delete and inspect the deleted rows in one statement.

Quick Quiz

Q1. Which DB2 clause limits the number of rows returned (replaces LIMIT / TOP)?

Show answer

FETCH FIRST n ROWS ONLY. It's the SQL standard form. Since DB2 11.1 you can also combine it with OFFSET n ROWS for pagination.

Q2. What does MERGE do that INSERT and UPDATE alone can't?

Show answer

MERGE is an upsert — it inspects a join condition and runs UPDATE if a matching row exists, or INSERT if it doesn't, all in one atomic statement. Without MERGE you'd need an explicit SELECT + branching logic.

Q3. What happens if you run DELETE FROM products with no WHERE clause?

Show answer

Every row in the products table is deleted. The table itself still exists (use DROP TABLE to remove the schema). Always double-check before running unfiltered DML — and use a transaction so you can ROLLBACK.

Next Up

CRUD lets you change data — next we add constraints so the database refuses changes that would break our rules.