INSERT, UPDATE, DELETE: Modifying Data Safely

Introduction

While SELECT queries read data, the DML (Data Manipulation Language) statements INSERT, UPDATE, and DELETE modify it. These three commands power every write operation in a Database Management System — registering a new user, updating a profile, cancelling an order, or purging old records. Writing correct, safe DML is critical because mistakes directly affect production data.

Explanation

INSERT adds new rows to a table. UPDATE modifies existing rows matching a condition. DELETE removes rows matching a condition. All three can affect multiple rows in a single operation and should always be wrapped in transactions when modifying related tables to ensure atomicity.

Real World Example

An e-commerce order placement involves multiple DML operations that must all succeed or all fail: INSERT a new order row, INSERT multiple order_item rows, UPDATE the products table to reduce stock_quantity, and UPDATE the customer's last_order_date. If any of these fails mid-way (e.g., a stock update fails because a product is discontinued), the entire operation must roll back to avoid a customer being charged for an order that was never fulfilled.

Technical Breakdown

INSERT syntax variations:

• Single row: INSERT INTO table (col1, col2) VALUES (val1, val2)

• Multiple rows: INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4), ...

• From SELECT: INSERT INTO table SELECT ... FROM another_table WHERE ...

UPDATE always requires a WHERE clause in production — an UPDATE without WHERE modifies every row in the table. Similarly, DELETE without WHERE deletes all rows (use TRUNCATE instead for full table clear, which is much faster).

Example

-- INSERT single row
INSERT INTO customers (name, email, city, registration_date)
VALUES ('Priya Sharma', 'priya@example.com', 'Bangalore', CURRENT_DATE);

-- INSERT multiple rows
INSERT INTO products (name, category, price, stock_quantity)
VALUES
    ('Laptop Pro 15', 'Electronics', 75999.00, 50),
    ('Wireless Keyboard', 'Electronics', 2499.00, 200),
    ('Office Chair', 'Furniture', 12999.00, 30);

-- UPDATE with WHERE (always include WHERE!)
UPDATE products
SET stock_quantity = stock_quantity - 1,
    updated_at = CURRENT_TIMESTAMP
WHERE product_id = 42
  AND stock_quantity > 0;  -- prevent negative stock

-- DELETE with condition
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;  -- clean up expired sessions

-- Safe pattern: wrap related DML in a transaction
BEGIN;
    INSERT INTO orders (customer_id, order_date, status)
    VALUES (101, CURRENT_DATE, 'pending');

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (LAST_INSERT_ID(), 42, 2, 75999.00);

    UPDATE products
    SET stock_quantity = stock_quantity - 2
    WHERE product_id = 42;
COMMIT;

Common Mistakes

• Running UPDATE or DELETE without a WHERE clause in production — always test your WHERE condition with a SELECT first, then convert to UPDATE/DELETE.

• Not using transactions when multiple DML statements must succeed together — partial failures leave the database in an inconsistent state.

• Inserting without specifying column names — INSERT INTO table VALUES (...) breaks when column order changes; always list column names explicitly.

• Using DELETE to clear a large table when TRUNCATE is more appropriate — TRUNCATE is orders of magnitude faster for full table clears.

Interview Tips

Interviewers often ask "How would you safely update a record in production?" — demonstrate awareness of: using transactions, testing the WHERE clause with SELECT first, using LIMIT 1 for single-row intended updates, and having a ROLLBACK plan. Also know the difference between DELETE (DML, transactional, triggers fire, can rollback) and TRUNCATE (DDL, not logged per row, faster, cannot rollback in most databases).