Part IV: Modifying Data with SQL

 

Inserting Records · Updating Records · Deleting Records

Modifying data—adding new rows, updating existing ones, and removing obsolete entries—is the flip side of querying. Without the ability to change data, databases become static archives. In this detailed guide, you’ll learn step by step how to insert, update, and delete records safely and efficiently in a relational database.

Inserting Records

Every database starts empty. The INSERT statement populates tables with new rows. You’ll discover how to add single records, bulk load data from other queries, and handle conflicts or defaults.

1. Single-Row Inserts

Use INSERT INTO … VALUES to add one row at a time. Always specify the column list to avoid mismatches when the schema changes.

sql

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Jane', 'Doe', 'jane.doe@example.com', '2025-08-15', 60000);

Key points:

• Match the order of columns and values exactly.

• Omit columns with defaults or NULL constraints if you want the database to supply them.

• If you accidentally leave out the column list, the database expects a value for every column in table order.

2. Bulk Inserts with INSERT … SELECT

To load multiple records from an existing table or subquery, use INSERT … SELECT. This is ideal when copying data between tables or staging imports.

sql

INSERT INTO archive_orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date < '2024-01-01';

Benefits:

• Efficient transfer of thousands or millions of rows in a single statement.

• Avoids round-trips between your application and the database.

3. Upserts and Conflict Handling

Modern databases like PostgreSQL support INSERT … ON CONFLICT to handle unique key violations gracefully. This pattern, often called “upsert,” either inserts a new row or updates an existing one.

sql

INSERT INTO products (product_id, name, price)
VALUES (101, 'Wireless Mouse', 29.99)
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price;

• EXCLUDED refers to the row you attempted to insert.

• Use conflict handling to maintain idempotent data loads and avoid duplicate key errors.

4. Best Practices for Inserting Data

• Always wrap bulk or multi-step inserts in transactions (BEGIN; … COMMIT;) to ensure atomicity.

• Use parameterized queries or prepared statements in your application to prevent SQL injection.

• For very large loads, consider database-specific bulk-load utilities (e.g., COPY in PostgreSQL, LOAD DATA in MySQL).

• Monitor disk space and commit log growth when inserting massive volumes.

Updating Records

Once data exists, you’ll often need to correct typos, adjust values, or apply business rules in bulk. The UPDATE statement modifies one or more columns in rows that meet a filter.

1. Basic UPDATE Syntax

sql

UPDATE employees
SET salary = salary * 1.05
WHERE performance_rating = 'A';

This example gives a 5% raise to top-performing employees. Notice the WHERE clause—omitting it would increase every salary.

2. Conditional and Complex Updates

Leverage SQL expressions, functions, and subqueries inside SET to perform conditional logic:

sql

UPDATE orders
SET status = CASE
    WHEN shipped_date IS NOT NULL THEN 'shipped'
    WHEN cancelled = TRUE THEN 'cancelled'
    ELSE 'pending'
  END;

Or update based on another table’s data:

sql

UPDATE o
SET o.discounted_price = p.price * 0.9
FROM order_items o
JOIN products p ON o.product_id = p.product_id
WHERE o.promotion_applied = TRUE;

Key takeaways:

• The CASE expression lets you apply different updates in one pass.

• The FROM clause (PostgreSQL syntax) or JOIN in MySQL/SQL Server enables join-based updates.

3. Previewing Updates Safely

Before running a mass update, preview affected rows with a SELECT using the same filter:

sql

SELECT employee_id, salary, salary * 1.05 AS new_salary
FROM employees
WHERE performance_rating = 'A';

Once satisfied, wrap the update in an explicit transaction so you can roll back if needed:

sql

BEGIN;
UPDATE employees
SET salary = salary * 1.05
WHERE performance_rating = 'A';
ROLLBACK;  -- or COMMIT;

4. Best Practices for UPDATE

• Always include a WHERE clause unless you truly intend to change every row.

• Use transactions to group related updates and maintain data integrity.

• Consider adding a last_updated timestamp column with a default trigger to track changes.

• Test updates on a development or staging copy before applying in production.

Deleting Records

Removing obsolete or erroneous data keeps your database lean and accurate. The DELETE statement removes rows; TRUNCATE quickly wipes entire tables.

1. Basic DELETE Syntax

sql

DELETE FROM sessions
WHERE last_activity < '2024-01-01';

This deletes all user sessions that became inactive before 2024. Like UPDATE, omitting WHERE deletes every row.

2. Truncate vs. Delete

Operation	Syntax	Behavior	Transactional	Triggers Fire?
DELETE	DELETE FROM table;	Row-by-row removal, logs each delete	Yes	Yes
TRUNCATE	TRUNCATE table;	Fast deallocation of entire table storage	Depends on DB	No

• Use TRUNCATE when you need to clear all rows quickly and don’t need granular rollback of each row.

• Use DELETE when you must conditionally remove rows or trigger cascading deletes via foreign keys.

3. Cascading Deletes and Referential Integrity

Define foreign keys with ON DELETE CASCADE to automatically remove child records:

sql

ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE;

When you delete from orders, all related order_items are removed in one shot, preventing orphaned rows.

4. Safe and Efficient Deletes

• Batch deletes on large tables to avoid long-running transactions:

  sql

  DELETE FROM logs
  WHERE log_date < '2024-01-01'
  LIMIT 10000;
  

  Loop this in a script until all old logs are purged.

• Archive before delete: move data to a history table for audit purposes:

  sql

  INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < '2024-01-01';
  DELETE FROM orders WHERE order_date < '2024-01-01';
  

• Use transactions to ensure that deletes and any related inserts (archiving) succeed or fail together.

Transactions and Error Handling

Modifying data often spans multiple statements. Use explicit transactions to guarantee all changes succeed or none at all:

sql

BEGIN;

-- Step 1: Insert a new order
INSERT INTO orders (…) VALUES (…);

-- Step 2: Update inventory
UPDATE inventory
SET stock_level = stock_level - 1
WHERE product_id = 101;

-- Step 3: Log the activity
INSERT INTO order_audit (…) VALUES (…);

COMMIT;

If any step fails, issue ROLLBACK; to revert all changes and maintain consistent data.

Common Pitfalls and How to Avoid Them

• Missing or overly broad WHERE clauses can wipe out or modify unintended rows.

• Long-running transactions can lock tables and impact application performance—keep them as short as possible.

• Ignoring foreign-key constraints leads to orphaned child records or cascade failures.

• Relying on implicit column order in INSERT risks breakage when schemas evolve.

Always preview updates/deletes with SELECT, test in non-production, and use transactions for safety.

Conclusion

Part IV equips you to take full control over your data—adding new entries, amending existing ones, and purging what’s stale. By following best practices like explicit transactions, careful use of WHERE clauses, and conflict handling, you’ll maintain data integrity and system performance. Next, we’ll dive into schema design, keys, and indexing strategies to prepare your database for scalable, efficient operations.