INSERT / UPDATE / DELETE

Category: Data Manipulation Language (DML)

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

INSERT adds new rows to a table, UPDATE modifies existing rows, and DELETE removes rows. These are the fundamental DML operations for changing data in tables. Essential for data maintenance, loading, and modifications.

INSERT Syntax

-- Insert single row
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Insert multiple rows
INSERT INTO table_name (column1, column2, ...)
VALUES 
    (value1a, value2a, ...),
    (value1b, value2b, ...),
    (value1c, value2c, ...);

-- Insert from SELECT
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

UPDATE Syntax

-- Update with condition
UPDATE table_name
SET 
    column1 = value1,
    column2 = value2
WHERE condition;

-- Update from another table
UPDATE table_name t1
SET column1 = (
    SELECT column2 
    FROM table2 t2 
    WHERE t2.id = t1.id
)
WHERE EXISTS (
    SELECT 1 FROM table2 t2 WHERE t2.id = t1.id
);

DELETE Syntax

-- Delete with condition
DELETE FROM table_name
WHERE condition;

-- Delete all rows (careful!)
DELETE FROM table_name;

-- Delete with subquery
DELETE FROM table_name
WHERE id IN (
    SELECT id FROM other_table WHERE condition
);

Platform-Specific Notes

Snowflake:

• Supports multi-table INSERT (INSERT ALL)
• UPDATE and DELETE are transactional
• Can use MERGE for upsert operations
• Supports INSERT OVERWRITE for replacing partitions

BigQuery:

• INSERT, UPDATE, DELETE require DML quota
• Supports INSERT with query results
• UPDATE and DELETE can be expensive on large tables
• Recommends MERGE for upserts
• Supports INSERT by partition

Databricks:

• Requires Delta Lake for UPDATE/DELETE on tables
• Supports MERGE for upserts
• INSERT OVERWRITE for partition replacement
• Optimized for large-scale operations

Example 1: INSERT - Single and Multiple Rows

All Platforms:

-- Insert single customer
INSERT INTO customers (customer_id, name, email, signup_date)
VALUES (101, 'John Smith', 'john@email.com', '2024-01-15');

-- Insert multiple customers
INSERT INTO customers (customer_id, name, email, signup_date)
VALUES 
    (102, 'Maria Garcia', 'maria@email.com', '2024-01-16'),
    (103, 'Sarah Johnson', 'sarah@email.com', '2024-01-17'),
    (104, 'Ahmed Hassan', 'ahmed@email.com', '2024-01-18');

customers table BEFORE:

customers table AFTER:

Example 2: INSERT with SELECT

All Platforms:

-- Copy high-value customers to VIP table
INSERT INTO vip_customers (customer_id, name, email, total_spent)
SELECT 
    c.customer_id,
    c.name,
    c.email,
    SUM(o.total) as total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email
HAVING SUM(o.total) > 1000;

Sample Data:

customers:

orders:

vip_customers table AFTER INSERT:

Example 3: UPDATE - Basic Updates

All Platforms:

-- Update single customer's email
UPDATE customers
SET 
    email = 'john.new@email.com',
    updated_at = CURRENT_TIMESTAMP
WHERE customer_id = 101;

-- Update multiple columns with calculation
UPDATE products
SET 
    price = price * 1.10,  -- 10% price increase
    last_updated = CURRENT_DATE
WHERE category = 'Electronics'
  AND price < 1000;

products table BEFORE:

products table AFTER:

Example 4: UPDATE with JOIN/Subquery

Snowflake:

-- Update customer tier based on total purchases
UPDATE customers c
SET tier = CASE 
    WHEN order_total.total >= 1000 THEN 'VIP'
    WHEN order_total.total >= 500 THEN 'Premium'
    ELSE 'Standard'
END
FROM (
    SELECT customer_id, SUM(total) as total
    FROM orders
    GROUP BY customer_id
) order_total
WHERE c.customer_id = order_total.customer_id;

BigQuery:

UPDATE customers c
SET tier = CASE 
    WHEN ot.total >= 1000 THEN 'VIP'
    WHEN ot.total >= 500 THEN 'Premium'
    ELSE 'Standard'
END
FROM (
    SELECT customer_id, SUM(total) as total
    FROM orders
    GROUP BY customer_id
) ot
WHERE c.customer_id = ot.customer_id;

Databricks:

MERGE INTO customers c
USING (
    SELECT customer_id, SUM(total) as total
    FROM orders
    GROUP BY customer_id
) ot
ON c.customer_id = ot.customer_id
WHEN MATCHED THEN UPDATE SET
    tier = CASE 
        WHEN ot.total >= 1000 THEN 'VIP'
        WHEN ot.total >= 500 THEN 'Premium'
        ELSE 'Standard'
    END;

customers table BEFORE:

customers table AFTER:

Example 5: DELETE - Conditional Deletion

All Platforms:

-- Delete old orders
DELETE FROM orders
WHERE order_date < '2023-01-01';

-- Delete customers without any orders
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id 
    FROM orders
);

-- Delete duplicate records (keep oldest)
DELETE FROM products
WHERE product_id IN (
    SELECT product_id
    FROM (
        SELECT 
            product_id,
            ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY created_date) as rn
        FROM products
    )
    WHERE rn > 1
);

orders table BEFORE:

orders table AFTER (deleted old orders):

Example 6: INSERT with Default and Computed Values

All Platforms:

-- Table with defaults and auto-increment
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'Pending',
    total DECIMAL(10,2)
);

-- Insert without specifying default columns
INSERT INTO orders (customer_id, total)
VALUES (101, 250.00);

-- order_id, order_date, and status will use defaults

INSERT / UPDATE / DELETE Comparison

MERGE (Upsert) Example

All Platforms:

MERGE INTO customer_summary cs
USING (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total) as total_spent,
        MAX(order_date) as last_order_date
    FROM orders
    GROUP BY customer_id
) o
ON cs.customer_id = o.customer_id
WHEN MATCHED THEN
    UPDATE SET
        cs.order_count = o.order_count,
        cs.total_spent = o.total_spent,
        cs.last_order_date = o.last_order_date,
        cs.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, order_count, total_spent, last_order_date, created_at)
    VALUES (o.customer_id, o.order_count, o.total_spent, o.last_order_date, CURRENT_TIMESTAMP);

Transaction Best Practices

All Platforms:

-- Use transactions for multiple operations
BEGIN TRANSACTION;

    -- Transfer money between accounts
    UPDATE accounts 
    SET balance = balance - 100 
    WHERE account_id = 'ACC001';
    
    UPDATE accounts 
    SET balance = balance + 100 
    WHERE account_id = 'ACC002';
    
    -- Log transaction
    INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
    VALUES ('ACC001', 'ACC002', 100, CURRENT_TIMESTAMP);

COMMIT;
-- Or ROLLBACK if error occurs

Common Use Cases

1. INSERT: Load new data, migrate records, create test data
2. UPDATE: Correct errors, apply business rules, update status
3. DELETE: Remove old data, clean duplicates, archive records
4. MERGE: Synchronize tables, incremental loads, upserts

Performance Tips

1. Batch operations: Insert/update/delete in batches, not row-by-row
2. Use MERGE: More efficient than separate INSERT/UPDATE
3. Disable indexes: For large bulk loads, disable then rebuild
4. Use TRUNCATE: Instead of DELETE for all rows (if appropriate)
5. Filter efficiently: Use indexes in WHERE clauses
6. Avoid triggers: Can slow down DML operations

Best Practices

1. Always use WHERE: For UPDATE and DELETE (avoid accidents)
2. Test on small datasets: Verify logic before large operations
3. Use transactions: For related operations
4. Backup before bulk changes: Safety first
5. Log changes: Track who changed what and when
6. Validate data: Check constraints and data quality
7. Consider soft deletes: Use status flags instead of DELETE