SQL DML Operations

DML (Data Manipulation Language) is used to retrieve, insert, update, and delete data in database tables. These are the most frequently used SQL operations.

DML Operations Overview

graph TB
    A[DML Operations] --> B[INSERT]
    A --> C[SELECT]
    A --> D[UPDATE]
    A --> E[DELETE]
    A --> F[MERGE]
    
    B --> B1[Add new rows]
    B --> B2[Single or bulk]
    
    C --> C1[Retrieve data]
    C --> C2[Query and filter]
    
    D --> D1[Modify existing rows]
    D --> D2[Conditional updates]
    
    E --> E1[Remove rows]
    E --> E2[Conditional deletion]
    
    F --> F1[Upsert operation]
    F --> F2[Insert, update, or delete]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336
    style F fill:#00BCD4

Key Points:

• INSERT: Adds new records to tables
• SELECT: Retrieves and queries data
• UPDATE: Modifies existing records
• DELETE: Removes records from tables
• MERGE: Combines insert, update, and delete in one operation

INSERT Operation Flow

sequenceDiagram
    participant App as Application
    participant DB as Database
    participant Table as Table
    participant Index as Indexes
    
    App->>DB: INSERT statement
    DB->>DB: Validate data types
    DB->>DB: Check constraints
    DB->>Table: Add new row
    Table->>Index: Update indexes
    Index->>DB: Confirm insertion
    DB->>App: Return success/row ID

Key Points:

• Validation: Data types and constraints checked
• Auto-increment: Primary key generated if needed
• Indexes: All indexes updated automatically
• Triggers: BEFORE/AFTER INSERT triggers executed
• Performance: Bulk inserts faster than individual

SELECT Query Execution Order

flowchart TB
    A[FROM] --> B[JOIN]
    B --> C[WHERE]
    C --> D[GROUP BY]
    D --> E[HAVING]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT/OFFSET]
    
    style A fill:#4CAF50
    style C fill:#FF9800
    style F fill:#2196F3
    style H fill:#9C27B0

Key Points:

• FROM/JOIN: Identify tables and combine data first
• WHERE: Filter rows before grouping
• GROUP BY: Aggregate rows into groups
• HAVING: Filter groups after aggregation
• SELECT: Choose columns to return
• ORDER BY: Sort final result set

UPDATE Operation Flow

sequenceDiagram
    participant App as Application
    participant DB as Database
    participant Table as Table
    participant Index as Indexes
    participant Log as Transaction Log
    
    App->>DB: UPDATE statement
    DB->>Log: Write to transaction log
    DB->>Table: Find matching rows
    Table->>Table: Lock rows
    Table->>Table: Update values
    Table->>Index: Update affected indexes
    Index->>DB: Confirm update
    DB->>App: Return rows affected

Key Points:

• WHERE Clause: Critical to avoid updating all rows
• Row Locking: Prevents concurrent modifications
• Transaction Log: Changes logged for rollback
• Indexes: Updated for modified columns
• Triggers: BEFORE/AFTER UPDATE triggers executed

DELETE Operation Flow

flowchart LR
    A[DELETE Statement] --> B{WHERE Clause?}
    B -->|Yes| C[Find matching rows]
    B -->|No| D[WARNING: All rows]
    
    C --> E[Lock rows]
    D --> E
    
    E --> F[Check foreign keys]
    F --> G{Constraints OK?}
    
    G -->|Yes| H[Delete rows]
    G -->|No| I[Error: FK violation]
    
    H --> J[Update indexes]
    J --> K[Return rows deleted]
    
    style D fill:#F44336
    style I fill:#F44336
    style H fill:#4CAF50

Key Points:

• WHERE Essential: Always use WHERE to avoid deleting all rows
• Foreign Keys: Checked before deletion
• Cascading: CASCADE option deletes related rows
• Soft Delete: Consider UPDATE with is_deleted flag
• Performance: DELETE slower than TRUNCATE for all rows

MERGE (UPSERT) Logic

flowchart TB
    A[MERGE Statement] --> B{Row Exists?}
    B -->|Yes| C[WHEN MATCHED]
    B -->|No| D[WHEN NOT MATCHED]
    
    C --> E{Update Condition?}
    E -->|True| F[UPDATE]
    E -->|False| G[DELETE]
    
    D --> H[INSERT]
    
    F --> I[Complete]
    G --> I
    H --> I
    
    style A fill:#2196F3
    style F fill:#FF9800
    style G fill:#F44336
    style H fill:#4CAF50

Key Points:

• Atomic Operation: Single statement for insert/update/delete
• Matching: Based on join condition (usually primary key)
• WHEN MATCHED: Update or delete existing rows
• WHEN NOT MATCHED: Insert new rows
• Performance: More efficient than separate operations

Transaction Safety

sequenceDiagram
    participant App as Application
    participant Trans as Transaction
    participant DB as Database
    
    App->>Trans: BEGIN TRANSACTION
    App->>DB: INSERT operation
    App->>DB: UPDATE operation
    App->>DB: DELETE operation
    
    alt All Success
        App->>Trans: COMMIT
        Trans->>DB: Save all changes
        DB->>App: Success
    else Any Failure
        App->>Trans: ROLLBACK
        Trans->>DB: Undo all changes
        DB->>App: Reverted
    end

Key Points:

• ACID: Ensures atomicity, consistency, isolation, durability
• BEGIN: Start transaction block
• COMMIT: Save all changes permanently
• ROLLBACK: Undo all changes in transaction
• Best Practice: Wrap related DML in transactions

Query Optimization

graph TB
    A[Query Performance] --> B[Use Indexes]
    A --> C[Limit Results]
    A --> D[Avoid SELECT *]
    A --> E[Use WHERE Early]
    
    B --> B1[Index WHERE columns]
    B --> B2[Index JOIN columns]
    
    C --> C1[Use LIMIT clause]
    C --> C2[Pagination with OFFSET]
    
    D --> D1[Select only needed columns]
    D --> D2[Reduce data transfer]
    
    E --> E1[Filter before JOIN]
    E --> E2[Reduce intermediate results]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#00BCD4

Key Points:

• Indexes: Create on frequently queried columns
• LIMIT: Restrict result set size for pagination
• Column Selection: Avoid SELECT *, specify needed columns
• Early Filtering: Apply WHERE before expensive operations
• EXPLAIN: Use to analyze query execution plan

Code Examples

INSERT Operations

-- Single row insert
INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');

-- Multiple rows insert (bulk)
INSERT INTO products (name, price, stock)
VALUES 
    ('Laptop', 999.99, 50),
    ('Mouse', 29.99, 200),
    ('Keyboard', 79.99, 150);

-- Insert from SELECT
INSERT INTO customers_backup
SELECT * FROM customers WHERE created_at < '2020-01-01';

-- UPSERT (MySQL)
INSERT INTO products (sku, name, price)
VALUES ('SKU001', 'Laptop', 999.99)
ON DUPLICATE KEY UPDATE 
    price = VALUES(price),
    updated_at = NOW();

-- UPSERT (PostgreSQL)
INSERT INTO products (sku, name, price)
VALUES ('SKU001', 'Laptop', 999.99)
ON CONFLICT (sku) 
DO UPDATE SET price = EXCLUDED.price;

SELECT Queries

-- Basic SELECT with WHERE
SELECT first_name, last_name, email
FROM customers
WHERE city = 'New York'
ORDER BY last_name;

-- Aggregate functions
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    SUM(stock) AS total_stock
FROM products
GROUP BY category
HAVING AVG(price) > 100;

-- JOIN query
SELECT 
    c.customer_name,
    o.order_id,
    o.total_amount,
    o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

-- Subquery
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

-- Pagination
SELECT * FROM customers
ORDER BY customer_id
LIMIT 20 OFFSET 40;  -- Page 3, 20 per page

UPDATE Operations

-- Single row update
UPDATE customers
SET email = '[email protected]'
WHERE customer_id = 123;

-- Multiple columns update
UPDATE products
SET price = price * 1.1, updated_at = NOW()
WHERE category = 'Electronics';

-- Update with CASE
UPDATE orders
SET status = CASE
    WHEN shipped_date IS NOT NULL THEN 'shipped'
    WHEN DATEDIFF(NOW(), order_date) > 30 THEN 'cancelled'
    ELSE 'pending'
END;

-- Update with JOIN
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
SET o.discount = 10
WHERE c.loyalty_tier = 'Gold';

DELETE Operations

-- Delete specific rows
DELETE FROM customers
WHERE customer_id = 123;

-- Delete with condition
DELETE FROM orders
WHERE order_date < '2020-01-01' AND status = 'cancelled';

-- Delete with subquery
DELETE FROM products
WHERE product_id IN (
    SELECT product_id FROM products
    WHERE stock = 0 AND last_updated < DATE_SUB(NOW(), INTERVAL 1 YEAR)
);

MERGE Operation

-- SQL Server MERGE
MERGE INTO products AS target
USING product_updates AS source
ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET 
        target.price = source.price,
        target.stock = source.stock
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, stock)
    VALUES (source.sku, source.name, source.price, source.stock);

Best Practices

1. Always Use WHERE: Never UPDATE/DELETE without WHERE clause
2. Use Transactions: Wrap related operations in BEGIN/COMMIT
3. Bulk Operations: Use multi-row INSERT instead of loops
4. Index Wisely: Index columns used in WHERE and JOIN
5. **Avoid SELECT ***: Specify only needed columns
6. Prepared Statements: Prevent SQL injection attacks
7. Test First: Use SELECT to verify WHERE before UPDATE/DELETE
8. Backup Data: Backup before bulk modifications

---