DML: Inserting, Updating and Deleting Data

6.1 INSERT

INSERT INTO customers (first_name, last_name, email, city) VALUES ('Asha', 'Rao', '[email protected]', 'Pune');

-- Multi-row VALUES (23ai table value constructor)
INSERT INTO categories (category_name) VALUES ('Electronics'), ('Books'), ('Toys');

-- INSERT ... SELECT (direct-path with APPEND hint)
INSERT /*+ APPEND */ INTO customer_archive SELECT * FROM customers WHERE created_at < DATE '2020-01-01';

-- Multi-table INSERT
INSERT ALL
  WHEN amount < 1000 THEN INTO small_orders VALUES (order_id, amount)
  WHEN amount >= 1000 THEN INTO big_orders VALUES (order_id, amount)
SELECT order_id, amount FROM order_totals;

INSERT FIRST
  WHEN region = 'N' THEN INTO north_sales
  WHEN region = 'S' THEN INTO south_sales
  ELSE INTO other_sales
SELECT * FROM sales;

-- RETURNING INTO (PL/SQL)
INSERT INTO customers (first_name, last_name) VALUES ('Ravi', 'Kumar') RETURNING customer_id INTO :new_id;

6.2 UPDATE

UPDATE products SET unit_price = unit_price * 1.10 WHERE category_id = 1;

-- Correlated update
UPDATE products p
SET stock = stock - (SELECT SUM(quantity) FROM order_items oi WHERE oi.product_id = p.product_id AND oi.order_id = 1001)
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id AND oi.order_id = 1001);

-- Update with join (23ai direct join syntax)
UPDATE products p
SET p.stock = p.stock - oi.quantity
FROM order_items oi
WHERE oi.product_id = p.product_id AND oi.order_id = 1001;

-- Updatable inline view (key-preserved)
UPDATE (SELECT p.stock, oi.quantity FROM products p JOIN order_items oi ON oi.product_id = p.product_id
        WHERE oi.order_id = 1001)
SET stock = stock - quantity;

-- RETURNING OLD and NEW values (23ai)
UPDATE products SET unit_price = unit_price * 0.9 WHERE product_id = 1
RETURNING OLD unit_price, NEW unit_price INTO :old_p, :new_p;

6.3 DELETE

DELETE FROM orders WHERE status = 'CANCELLED' AND order_date < DATE '2023-01-01';
DELETE FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'Testland');

6.4 MERGE (Upsert)

MERGE INTO products tgt
USING product_staging src
ON (tgt.product_id = src.product_id)
WHEN MATCHED THEN
    UPDATE SET tgt.unit_price = src.unit_price
    WHERE tgt.unit_price <> src.unit_price
    DELETE WHERE src.discontinued = 'Y'
WHEN NOT MATCHED THEN
    INSERT (product_name, category_id, unit_price) VALUES (src.product_name, src.category_id, src.unit_price)
    WHERE src.unit_price > 0;

6.5 DML Error Logging

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('CUSTOMERS');   -- creates ERR$_CUSTOMERS
INSERT INTO customers SELECT * FROM customers_stage
LOG ERRORS INTO err$_customers ('load_2025_10') REJECT LIMIT UNLIMITED;

6.6 Conventional vs Direct-Path Insert

Direct path (/*+ APPEND */, CTAS, SQL*Loader direct, Data Pump) writes above the high-water mark, bypasses the buffer cache, and can be NOLOGGING.

It takes an exclusive table lock until commit. The session can't query the table before committing (ORA-12838).