Paradime | INSERT / UPDATE / DELETE

SQL Keywords

INSERT / UPDATE / DELETE

Feb 23, 2026

min read

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

-- 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

-- 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
)

-- 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
)

-- 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
)

-- 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
)

-- 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')

-- 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')

-- 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:

customer_id	name	email	signup_date
(empty)

customers table AFTER:

customer_id	name	email	signup_date
101	John Smith	john@email.com	2024-01-15
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

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

-- 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

-- 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:

customer_id	name	email
101	John Smith	john@email.com
102	Maria Garcia	maria@email.com

orders:

order_id	customer_id	total
1	101	600.00
2	101	500.00
3	102	300.00

vip_customers table AFTER INSERT:

customer_id	name	email	total_spent
101	John Smith	john@email.com	1100.00

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

-- 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

-- 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:

product_id	product_name	category	price	last_updated
1	Mouse	Electronics	29.99	2023-12-01
2	Keyboard	Electronics	89.99	2023-12-01
3	Desk	Furniture	299.99	2023-12-01

products table AFTER:

product_id	product_name	category	price	last_updated
1	Mouse	Electronics	32.99	2024-01-20
2	Keyboard	Electronics	98.99	2024-01-20
3	Desk	Furniture	299.99	2023-12-01

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

-- 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

-- 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

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

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

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

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

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

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:

customer_id	name	tier
101	John Smith	Standard
102	Maria Garcia	Standard

customers table AFTER:

customer_id	name	tier
101	John Smith	VIP
102	Maria Garcia	Standard

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
)

-- 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
)

-- 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:

order_id	order_date	total
1	2022-06-15	250.00
2	2023-01-20	180.00
3	2024-01-25	320.00

orders table AFTER (deleted old orders):

order_id	order_date	total
2	2023-01-20	180.00
3	2024-01-25	320.00

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

-- 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

-- 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

Operation	Purpose	Affects	Returns
INSERT	Add new rows	0 to many rows	Row count
UPDATE	Modify existing rows	0 to many rows	Row count
DELETE	Remove rows	0 to many rows	Row count
MERGE	Upsert (insert or update)	0 to many rows	Row count

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)

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)

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

-- 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

-- 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

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

Performance Tips

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

Best Practices

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

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources