Module 4

Module 4: Modifying Data – Making Applications Interactive

So far, we’ve focused on reading data (SELECT). But applications aren’t static; users sign up, products are added, statuses change, items are removed. This module covers the SQL commands used to modify data: INSERT, UPDATE, and DELETE. Mastering these is key to building interactive applications.

⚠️ A Word of Caution: Modifying data is inherently more dangerous than reading it. An incorrect UPDATE or DELETE statement (especially without a proper WHERE clause) can permanently alter or destroy large amounts of data. Always be careful, understand your WHERE clauses, and ideally, test modifications in a non-production environment first!

Lesson 4.1: Adding Data: INSERT

The INSERT statement adds new rows (records) to a table.

Application Scenario:

  • A new user completes the signup form -> INSERT into users.
  • An admin adds a new product to the catalog -> INSERT into products.
  • A customer places an order -> INSERT into orders, then INSERT into order_items for each item.

Syntax (Recommended Form):

SQL

INSERT INTO tablename (column1, column2, column3)
VALUES (value1, value2, value3);
  • INSERT INTO tablename: Specifies the table to add data to.
  • (column1, column2, column3): Lists the specific columns you want to provide values for.
  • VALUES (value1, value2, value3): Provides the corresponding values for the listed columns. The order of values must match the order of columns.

Key Points:

  • Data Types: Values must match the data type of the column (e.g., text in single quotes 'like this', numbers 123.45 without quotes).
  • Auto-Increment Keys: You usually do not provide a value for an auto-incrementing Primary Key column (like user_id, product_id). The database assigns the next available ID automatically.
  • Default Values: If a column has a default value defined (like registration_date or order_date in our schema), you can omit it from the INSERT statement, and the database will use the default.
  • NULL Values: If a column allows NULLs and you want to insert a NULL, you can either omit the column from the list or use the keyword NULL in the VALUES list.
  • Foreign Keys: When inserting a row with a Foreign Key (like products.category_id), the value you provide must exist as a Primary Key in the referenced table (categories.category_id).

Example 1: Add a new user

SQL

INSERT INTO users (first_name, last_name, email)
VALUES ('Diana', 'Prince', 'diana.p@email.com');
-- user_id and registration_date will be handled automatically by the DB

Example 2: Add a new product category

SQL

INSERT INTO categories (category_name)
VALUES ('Groceries');
-- category_id is auto-incremented

Example 3: Add a new product (assuming ‘Electronics’ category_id is 1)

SQL

INSERT INTO products (product_name, description, price, stock_quantity, category_id)
VALUES ('USB-C Hub', '7-port USB-C Hub with HDMI', 49.99, 75, 1);
-- product_id is auto-incremented

(Syntax Alternative – Less Safe): INSERT INTO tablename VALUES (value1, value2, ...); This requires providing a value for every column in the exact order they appear in the table definition. It’s less readable and breaks easily if the table structure changes. Stick to specifying column names.

Try it yourself:

  1. Add a new category called ‘Footwear’.
  2. Add a new user named ‘Clark’ ‘Kent’ with email ‘clark.k@email.com’.

Lesson 4.2: Updating Data: UPDATE

The UPDATE statement modifies existing rows in a table.

Application Scenario:

  • A user changes their shipping address on their profile page -> UPDATE the users table.
  • An admin increases the price of a product -> UPDATE the products table.
  • A background job reduces the stock_quantity after an item ships -> UPDATE the products table.

Syntax:

SQL

UPDATE tablename
SET column1 = new_value1,
    column2 = new_value2
WHERE condition; -- VERY IMPORTANT!
  • UPDATE tablename: Specifies the table to update.
  • SET column1 = new_value1, ...: Specifies which columns to change and their new values. You can update one or multiple columns.
  • WHERE condition;: Specifies which rows to update. This works exactly like the WHERE clause in SELECT.

🚨 DANGER ZONE: The WHERE Clause is CRITICAL! 🚨 If you forget the WHERE clause in an UPDATE statement, it will update ALL ROWS in the table. This is rarely intended and can cause massive data corruption. ALWAYS double-check your WHERE clause before running an UPDATE.

Example 1: Update a specific user’s email address (First, you’d typically find the user’s ID, e.g., SELECT user_id FROM users WHERE email = 'alice.s@email.com'; Let’s assume Alice’s user_id is 1).

SQL

UPDATE users
SET email = 'alice.smith.updated@email.com'
WHERE user_id = 1; -- Update only the row where user_id is 1

Example 2: Increase the price of a specific product by 10% (Assume the ‘Laptop Pro 15″‘ product_id is 1).

SQL

UPDATE products
SET price = price * 1.10 -- Calculate the new value based on the old one
WHERE product_id = 1;

Example 3: Decrease stock for a product after a sale (Assume product_id 3 was sold, quantity 1).

SQL

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 3;

Example 4: Put all products in category 2 (‘Books’) on sale (20% off)

SQL

UPDATE products
SET price = price * 0.80
WHERE category_id = 2; -- Updates multiple rows matching the condition

Try it yourself:

  1. Find the user_id for ‘Bob Johnson’. Update his last name to ‘Williams’.
  2. Increase the stock_quantity of the ‘Wireless Mouse’ (find its product_id first) by 50 units.
  3. Change the shipping_address for order with order_id = 1 to ‘456 Oak Ave, Anytown’.

Lesson 4.3: Deleting Data: DELETE

The DELETE statement removes rows from a table.

Application Scenario:

  • A user requests account deletion -> DELETE from users.
  • An admin removes a discontinued product -> DELETE from products.
  • A moderation tool removes an inappropriate comment -> DELETE from comments.

Syntax:

SQL

DELETE FROM tablename
WHERE condition; -- EXTREMELY IMPORTANT!
  • DELETE FROM tablename: Specifies the table to remove rows from.
  • WHERE condition;: Specifies which rows to delete.

🚨 DANGER ZONE: The WHERE Clause is CRITICAL! 🚨 Just like UPDATE, if you forget the WHERE clause in a DELETE statement, it will delete ALL ROWS from the table. This is usually irreversible without backups. ALWAYS triple-check your WHERE clause before running a DELETE.

Example 1: Delete a specific user (Assume Diana Prince’s user_id is 4).

SQL

DELETE FROM users
WHERE user_id = 4;

Note: Our orders table has ON DELETE CASCADE for user_id. This means deleting user 4 would automatically delete any orders associated with user 4.

Example 2: Delete all orders placed before a specific date

SQL

DELETE FROM orders
WHERE order_date < '2024-01-01'; -- Example date

Note: Our order_items table also has ON DELETE CASCADE for order_id. Deleting these orders would also delete their corresponding line items.

Example 3: Attempt to delete a product referenced in an order (Assume product_id 1 exists in the order_items table).

SQL

DELETE FROM products
WHERE product_id = 1;

This query would likely FAIL. Why? Because our order_items table’s Foreign Key constraint for product_id is set to ON DELETE RESTRICT. This prevents you from deleting a product that is referenced by existing order items, ensuring data integrity.

Soft Deletes: A Safer Alternative

Because DELETE is permanent and can have cascading effects (or be blocked by constraints), many applications use a “soft delete” pattern. Instead of actually deleting the row, you mark it as inactive.

  • Add a column like is_active BOOLEAN DEFAULT TRUE or deleted_at TIMESTAMP NULL to the table.
  • To “delete” a record, you UPDATE it: SET is_active = FALSE or SET deleted_at = CURRENT_TIMESTAMP.
  • Your SELECT queries then need to include WHERE is_active = TRUE or WHERE deleted_at IS NULL to only retrieve active records.

Pros: Preserves history, easily reversible, avoids foreign key deletion issues. Cons: Requires modifying queries, inactive data remains in the table (though usually filtered out).

Try it yourself:

  1. Delete the category ‘Footwear’ you added earlier (assuming no products reference it). Find its category_id first.
  2. Delete the order with order_id = 2 (check the sample data for valid IDs).

Lesson 4.4: (Optional but Recommended) Transactions

What happens if an application needs to perform multiple related data modifications? Consider placing an order:

  1. INSERT a new row into the orders table.
  2. INSERT one or more rows into the order_items table.
  3. UPDATE the stock_quantity in the products table for each item ordered.

These steps must succeed or fail together. If step 1 succeeds but step 3 fails (e.g., not enough stock), you don’t want the order recorded without the stock being adjusted, or vice versa. You want the entire operation to be atomic – all or nothing.

Transactions ensure atomicity. A transaction is a sequence of SQL operations performed as a single logical unit of work.

Key Commands:

  • BEGIN TRANSACTION; (or START TRANSACTION;): Marks the beginning of a transaction.
  • COMMIT;: Makes all changes within the transaction permanent and visible to others. Ends the transaction.
  • ROLLBACK;: Undoes all changes made since the BEGIN TRANSACTION; statement. Ends the transaction.

Conceptual Flow for Placing an Order:

SQL

BEGIN TRANSACTION;

-- Attempt to insert the main order record
INSERT INTO orders (user_id, total_amount, shipping_address) VALUES ( ... );
-- Check for errors... if error, ROLLBACK;

-- Attempt to insert order items
INSERT INTO order_items (order_id, product_id, quantity, price_per_unit) VALUES ( ... );
-- Check for errors... if error, ROLLBACK;
INSERT INTO order_items (order_id, product_id, quantity, price_per_unit) VALUES ( ... );
-- Check for errors... if error, ROLLBACK;

-- Attempt to update stock levels
UPDATE products SET stock_quantity = stock_quantity - ordered_quantity WHERE product_id = ... AND stock_quantity >= ordered_quantity;
-- Check if update affected 1 row (meaning stock was sufficient)... if not, ROLLBACK;
UPDATE products SET stock_quantity = stock_quantity - ordered_quantity WHERE product_id = ... AND stock_quantity >= ordered_quantity;
-- Check if update affected 1 row... if not, ROLLBACK;

-- If all steps succeeded without errors:
COMMIT;

-- If any step resulted in a ROLLBACK, the database returns to the state it was in before BEGIN TRANSACTION.

(Note: Real application code handles the error checking and commit/rollback logic).

ACID Properties: Transactions provide guarantees known as ACID:

  • Atomicity: All operations complete successfully, or none of them do.
  • Consistency: The database remains in a valid state before and after the transaction.
  • Isolation: Concurrent transactions do not interfere with each other (managed by the DBMS).
  • Durability: Once a transaction is committed, the changes are permanent, even if the system crashes.

Understanding transactions is vital for building reliable applications that perform complex operations involving multiple data modifications.

Mini-Project 4: Modifying Store Data

Let’s practice making changes to our E-commerce database. Remember to use WHERE clauses carefully! You may need to run SELECT queries first to find the correct IDs.

  1. Add Category: Add a new category named ‘Sports Equipment’. (Run a SELECT query afterwards to verify it was added and find its category_id).
  2. Add Product: Add a new product: ‘Yoga Mat’, description ‘Comfortable exercise mat’, price 29.99, stock_quantity 50. Assign it to the ‘Sports Equipment’ category you just created (use the category_id you found). (Verify with SELECT).
  3. Update User: User ‘Bob Williams’ (who was ‘Bob Johnson’ – find his user_id) wants his first name updated to ‘Robert’. Update his record. (Verify with SELECT).
  4. Delete Order: Delete the specific order with order_id = 3 (or another valid order_id from your sample data). (Verify with SELECT – it should be gone).

You’ve now learned how to use INSERT, UPDATE, and DELETE to manipulate data, making your applications interactive. You also understand the critical importance of the WHERE clause and the concept of transactions for ensuring data integrity during complex operations. In the next module, we’ll look at some more advanced querying techniques.