Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 4

Modifying Data and Functions: UPDATE, DELETE, ALTER TABLE, Views, CASE WHEN, and NULL Handling

A hands-on lesson covering how to change existing data, remove rows, alter table structure, save reusable queries as views, apply conditional logic in SQL, and handle missing values with COALESCE and IFNULL.

So far in this bootcamp you have learned to create databases and tables, and to retrieve data with SELECT and WHERE. But data is not static. Prices change. Customer addresses get updated. Old records need archiving. Table structures evolve as business requirements shift. And real data is never complete, there are always missing phone numbers, blank email fields, and NULL values that break your calculations if you do not handle them. This lesson teaches you to modify data safely, reshape tables without losing information, create reusable query views, apply conditional logic directly in SQL, and protect your queries from the chaos of NULL. These are the skills that turn a query writer into a database professional.

1. UPDATE Statement: Changing Existing Data

The UPDATE statement modifies existing rows in a table. It is one of the most dangerous commands in SQL because a single mistake can change thousands of rows at once. There is no undo button. The only recovery is a backup. Treat every UPDATE as a potential disaster until you prove otherwise.

The basic UPDATE syntax. To change a specific value, you specify the table, the column, the new value, and which rows to affect:

UPDATE customers
SET email = 'chidinma.okafor@newmail.com'
WHERE customer_id = 42;

This changes the email address for exactly one customer, the one with customer_id 42. The WHERE clause is not optional in practice. If you omit it, every single row in the table gets the same email address, and you have just overwritten your entire customer database. Always write the WHERE clause first.

Updating multiple columns at once. You can change several columns in the same UPDATE by separating them with commas:

UPDATE customers
SET email = 'chidinma.okafor@newmail.com',
    phone = '08031234567',
    is_active = TRUE
WHERE customer_id = 42;

All three columns are updated for the same row in a single operation. This is more efficient than running three separate UPDATE statements.

Updating based on calculations. You can use expressions and even reference other columns in the SET clause:

UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

This increases the price of every electronics product by 10 percent. The expression price * 1.10 is evaluated for each matching row individually, using that row's current price.

The safety rule: SELECT before UPDATE. Before running any UPDATE, especially one that affects multiple rows, run a SELECT with the exact same WHERE clause to preview what you are about to change:

SELECT * FROM customers WHERE customer_id = 42;
-- Verify this is the correct row
UPDATE customers
SET email = 'chidinma.okafor@newmail.com'
WHERE customer_id = 42;

If the SELECT returns the wrong rows, fix your WHERE clause before running UPDATE. This habit has saved more databases than any backup strategy.

UPDATE with JOIN. Sometimes you need to update one table based on data from another table. In MySQL, you can JOIN inside an UPDATE:

UPDATE customers c
JOIN regions r ON c.region_id = r.region_id
SET c.region_name = r.region_name
WHERE r.is_active = TRUE;

This updates the region_name in the customers table by pulling the current name from the regions table, but only for active regions. JOIN updates are powerful but complex. Test them on a copy of your data first.

2. DELETE Statement: Removing Rows

DELETE removes rows from a table permanently. Like UPDATE, it is dangerous without a precise WHERE clause. A DELETE without WHERE empties the entire table. Unlike dropping a table, DELETE removes only the data and leaves the table structure intact.

The basic DELETE syntax.

DELETE FROM customers
WHERE customer_id = 42;

This removes exactly one row. The FROM keyword is required in standard SQL, though MySQL allows you to omit it. Include it for clarity and portability.

DELETE with conditions. You can delete multiple rows that match a condition:

DELETE FROM orders
WHERE order_date < '2024-01-01'
  AND status = 'cancelled';

This removes all cancelled orders older than January 1, 2024. Before running this, run the equivalent SELECT to count how many rows will be affected:

SELECT COUNT(*) FROM orders
WHERE order_date < '2024-01-01'
  AND status = 'cancelled';

If the count is 50,000 and you expected 50, you know your WHERE clause is wrong. Fix it before deleting.

Soft deletes versus hard deletes. In production databases, many teams avoid DELETE entirely and instead use a soft delete. They add a column like is_deleted or deleted_at, and instead of removing the row, they mark it:

UPDATE customers
SET is_deleted = TRUE,
    deleted_at = NOW()
WHERE customer_id = 42;

The row remains in the table but is excluded from normal queries by adding AND is_deleted = FALSE to every SELECT. Soft deletes preserve audit trails, allow recovery of accidentally removed data, and maintain referential integrity with related tables. The trade-off is slightly larger tables and the need to remember the is_deleted filter in every query. For analytical work, soft deletes are usually overkill. For transactional systems handling customer data, they are standard practice.

TRUNCATE: the nuclear option. If you need to remove every row from a table and reset auto-increment counters, use TRUNCATE instead of DELETE:

TRUNCATE TABLE orders;

TRUNCATE is faster than DELETE for large tables because it does not log individual row deletions. However, it cannot be rolled back in most configurations, and it cannot be used on tables with foreign key constraints. Use it only when you are certain you want to start completely fresh.

3. ALTER TABLE: Add, Drop, and Modify Columns

Tables are not carved in stone. Business requirements change, and your database structure must adapt. ALTER TABLE is the command that reshapes existing tables without recreating them from scratch. In MySQL 8.0, many ALTER TABLE operations are performed online, meaning they do not lock the table or require a full rebuild, which is critical for production databases with millions of rows.

Adding a column. To add a new column to an existing table:

ALTER TABLE customers
ADD COLUMN loyalty_points INT DEFAULT 0;

This adds a loyalty_points column at the end of the table, with a default value of 0 for all existing rows. If you omit DEFAULT, existing rows get NULL in the new column. If you add NOT NULL without a DEFAULT, MySQL will reject the statement unless the table is empty, because it cannot populate existing rows with a valid value.

Adding a column at a specific position. By default, new columns are appended at the end. You can control placement:

ALTER TABLE customers
ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

ALTER TABLE customers
ADD COLUMN prefix VARCHAR(10) FIRST;

The AFTER keyword places the new column immediately after the specified existing column. FIRST places it at the very beginning, before all other columns. Column order is mostly cosmetic, but it affects how DESCRIBE displays the table and can make manual data inspection more intuitive.

Adding multiple columns in one statement. You can combine multiple changes to minimize table rebuilds:

ALTER TABLE customers
ADD COLUMN date_of_birth DATE AFTER last_name,
ADD COLUMN gender VARCHAR(10) AFTER date_of_birth,
ADD COLUMN referral_code VARCHAR(20);

This is more efficient than running three separate ALTER TABLE statements because MySQL performs only one table rebuild.

Dropping a column. To remove a column permanently:

ALTER TABLE customers
DROP COLUMN legacy_id;

This removes the column and all its data. If the column is part of an index, the index is automatically adjusted. If the column is referenced by a foreign key, MySQL will block the drop until you remove the foreign key constraint first. In MySQL 8.0.29 and later, DROP COLUMN uses ALGORITHM=INSTANT, making it nearly instantaneous even on large tables.

Modifying a column's data type. To change the data type or constraints of an existing column:

ALTER TABLE customers
MODIFY COLUMN phone VARCHAR(15);

This changes the phone column from whatever it was to VARCHAR(15). Be careful: MODIFY drops and recreates the column definition, which means you must restate all existing attributes like NOT NULL, DEFAULT, and COMMENT, or they will be lost.

Renaming a column. To rename a column while preserving its data type and constraints:

ALTER TABLE customers
RENAME COLUMN email TO email_address;

MySQL automatically updates indexes and foreign keys that reference the old column name. However, views and stored procedures that reference the old name must be updated manually.

ALGORITHM hints for large tables. On production databases with millions of rows, specify the algorithm explicitly to avoid unexpected table locks:

ALTER TABLE large_orders
ADD COLUMN priority VARCHAR(10),
ALGORITHM=INSTANT;

If INSTANT is not supported for your specific operation, MySQL returns an error instead of silently falling back to a slower method. This lets you choose a maintenance window rather than locking a busy table unexpectedly.

4. CREATE VIEW: Saving a Query as a Virtual Table

A view is a saved SELECT query that behaves like a table. It does not store data itself. It stores the query definition, and every time you query the view, MySQL runs the underlying SELECT and returns fresh results. Views are powerful for simplifying complex queries, enforcing consistent business logic, and controlling what data different users can see.

Creating a simple view. Suppose you frequently need a list of active customers with their full names and regions:

CREATE VIEW active_customers AS
SELECT customer_id,
       CONCAT(first_name, ' ', last_name) AS full_name,
       region,
       email
FROM customers
WHERE is_active = TRUE;

Now you can query the view exactly like a table:

SELECT * FROM active_customers WHERE region = 'Lagos';

MySQL executes the view's underlying SELECT, applies the additional WHERE clause, and returns only active customers in Lagos. The view hides the complexity of the CONCAT and the is_active filter from anyone using it.

Views for security and access control. You can grant a user permission to query a view while denying them access to the underlying table. This lets you expose only specific columns or rows. For example, a view that shows customer names and regions but hides email and phone numbers:

CREATE VIEW customer_directory AS
SELECT customer_id, first_name, last_name, region
FROM customers;

A user with SELECT permission on customer_directory but not on customers can see names and regions but cannot access emails, phone numbers, or other sensitive data. This is a common pattern in multi-tenant applications and reporting systems.

Updating through views. Some views are updatable, meaning you can run INSERT, UPDATE, or DELETE on the view and MySQL applies the change to the underlying table. A view is updatable if it is based on a single table, contains no aggregate functions, no DISTINCT, no GROUP BY, and no UNION. However, many teams avoid updating through views because it obscures which table is actually being modified. Treat views as read-only unless you have a specific reason to do otherwise.

Replacing a view. If you need to change a view's definition, use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id,
       CONCAT(first_name, ' ', last_name) AS full_name,
       region,
       email,
       registration_date
FROM customers
WHERE is_active = TRUE;

This updates the view definition without dropping and recreating it, so any permissions granted on the view remain intact.

Dropping a view.

DROP VIEW IF EXISTS active_customers;

5. CASE WHEN: Conditional Logic in SQL

SQL is not just for retrieving and storing data. It can also make decisions. The CASE expression is SQL's version of an if-then-else statement. It evaluates conditions and returns different values based on which condition is true. CASE is incredibly versatile: you can use it in SELECT, WHERE, ORDER BY, and even inside aggregate functions.

Simple CASE syntax. The simplest form compares one expression against multiple possible values:

SELECT first_name, last_name,
       CASE region
           WHEN 'Lagos' THEN 'South-West'
           WHEN 'Abuja' THEN 'North-Central'
           WHEN 'Port Harcourt' THEN 'South-South'
           ELSE 'Other'
       END AS geo_zone
FROM customers;

This creates a new column called geo_zone that maps each region to a broader geopolitical zone. The ELSE clause provides a fallback for regions not explicitly listed. If you omit ELSE and no condition matches, CASE returns NULL.

Searched CASE syntax. The more powerful form evaluates independent boolean expressions:

SELECT order_id, total_amount,
       CASE
           WHEN total_amount >= 100000 THEN 'High Value'
           WHEN total_amount >= 50000 THEN 'Medium Value'
           WHEN total_amount >= 10000 THEN 'Low Value'
           ELSE 'Micro Value'
       END AS order_tier
FROM orders;

This categorizes orders into tiers based on their total amount. Notice that the conditions are evaluated in order. A 75,000 naira order matches the second condition, not the third, because the first true condition wins. Order matters. Put your most specific or highest-threshold conditions first.

CASE in aggregate functions. One of the most powerful uses of CASE is inside SUM and COUNT to create conditional aggregates:

SELECT region,
       COUNT(*) AS total_customers,
       SUM(CASE WHEN is_active = TRUE THEN 1 ELSE 0 END) AS active_customers,
       SUM(CASE WHEN is_active = FALSE THEN 1 ELSE 0 END) AS inactive_customers
FROM customers
GROUP BY region;

This produces a summary showing total, active, and inactive customers per region in a single query. Without CASE, you would need three separate queries or a complex self-join.

CASE in ORDER BY. You can use CASE to create custom sort orders that are not alphabetical or numeric:

SELECT * FROM orders
ORDER BY CASE status
             WHEN 'urgent' THEN 1
             WHEN 'pending' THEN 2
             WHEN 'shipped' THEN 3
             WHEN 'delivered' THEN 4
             ELSE 5
         END;

This sorts orders by business priority rather than alphabetical status name. Urgent orders appear first, delivered orders last, regardless of what the status names would sort to alphabetically.

6. COALESCE and IFNULL: Handling NULL Values

NULL is the absence of a value. It is not zero, not an empty string, not false. It is nothing. And nothing causes more bugs in SQL than NULL. Any calculation involving NULL returns NULL. Any comparison with NULL returns unknown, not true or false. If you do not handle NULL explicitly, your reports will silently produce wrong numbers. COALESCE and IFNULL are your primary defenses.

IFNULL: the simple two-argument fallback. IFNULL takes two arguments. If the first is not NULL, it returns the first. If the first is NULL, it returns the second:

SELECT first_name,
       IFNULL(phone, 'No phone on file') AS contact_phone
FROM customers;

If a customer has a phone number, it displays. If phone is NULL, the text "No phone on file" appears instead. This makes reports readable and prevents NULL from propagating into downstream calculations.

COALESCE: the multi-argument chain. COALESCE accepts any number of arguments and returns the first one that is not NULL. It is the SQL standard way to handle missing values and is more flexible than IFNULL, which only accepts two arguments:

SELECT first_name,
       COALESCE(phone, email, 'No contact info') AS best_contact
FROM customers;

This returns the phone number if it exists. If phone is NULL, it tries email. If both are NULL, it returns "No contact info." COALESCE evaluates arguments left to right and stops at the first non-NULL value. All arguments should be of compatible types. Mixing a VARCHAR and an INT will cause errors or unexpected conversions.

COALESCE in calculations. NULL breaks arithmetic. Any number plus NULL equals NULL. Use COALESCE to substitute zero or another sensible default:

SELECT order_id,
       total_amount,
       COALESCE(discount, 0) AS discount,
       total_amount - COALESCE(discount, 0) AS net_amount
FROM orders;

If discount is NULL, COALESCE substitutes 0, and the net_amount calculation proceeds correctly. Without COALESCE, net_amount would be NULL for every row with a missing discount, which would silently corrupt your revenue totals.

COALESCE in JOIN results. LEFT JOINs produce NULL for non-matching rows. COALESCE provides readable defaults:

SELECT e.name,
       COALESCE(d.dept_name, 'Unassigned') AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Employees without a department show "Unassigned" instead of NULL. This is cleaner for reports and prevents NULL from causing issues in application code that consumes the query results.

COALESCE versus CASE WHEN for NULL handling. COALESCE is a shorthand for a specific CASE expression. These two queries produce identical results:

-- Using COALESCE
SELECT COALESCE(phone, email, 'No contact') FROM customers;

-- Equivalent CASE
SELECT CASE
           WHEN phone IS NOT NULL THEN phone
           WHEN email IS NOT NULL THEN email
           ELSE 'No contact'
       END
FROM customers;

Use COALESCE when the logic is purely about finding the first non-NULL value. Use CASE WHEN when the logic involves conditions beyond NULL checks, such as ranges, text patterns, or multiple interdependent rules.

Sorting NULLs to the end. By default, MySQL sorts NULL values first in ascending order. To force NULLs to the end, use COALESCE with a sentinel value:

SELECT name, credit_limit
FROM customers
ORDER BY COALESCE(credit_limit, 0) DESC;

This sorts by credit_limit in descending order, but rows with NULL credit limits get a substituted value of 0, placing them at the bottom instead of the top.

Using AI to Move Faster in Data Modification

The commands in this lesson are straightforward individually, but combining them safely in production requires care. AI can help you write correct syntax, preview the impact of changes, and handle NULL logic that would otherwise take multiple attempts to get right.

1. Generate UPDATE statements from business requirements.
When a stakeholder says "Increase all electronics prices by 15 percent but cap the increase at 5,000 naira," you can describe this to Copilot: "Write a MySQL UPDATE statement that increases the price column by 15 percent for rows where category is 'Electronics', but the new price should not exceed the old price plus 5,000." AI will likely generate something using LEAST(price * 1.15, price + 5000). Review the logic, test it on a copy of the data, then execute on production.

2. Use AI to validate your WHERE clause before DELETE.
Paste your intended DELETE statement into an AI assistant and ask: "Before I run this DELETE, what rows will it affect? Show me the equivalent SELECT COUNT(*) and SELECT * LIMIT 5 queries." AI will generate the preview queries automatically. Run them, verify the count and sample rows, and only then execute the DELETE. This workflow prevents the catastrophic DELETE without WHERE mistake that every DBA fears.

3. Design ALTER TABLE scripts with AI assistance.
When you need to modify a production table, describe the change to AI: "I need to add a priority column to an orders table with 10 million rows. It should be VARCHAR(10), default 'normal', and placed after the status column. Write the ALTER TABLE statement with the safest algorithm hint for MySQL 8.0." AI will suggest ALGORITHM=INSTANT and include the AFTER clause. It may also warn you about adding NOT NULL without a DEFAULT, which would fail on a populated table. This catches schema design errors before they lock your database.

4. Convert complex business rules into CASE expressions.
Tiered pricing, commission brackets, and performance ratings all involve conditional logic that is easy to express in English but tedious to write in SQL. Describe the rule: "Write a CASE expression that categorizes orders as 'High Value' if over 100,000, 'Medium' if 50,000 to 100,000, 'Low' if 10,000 to 50,000, and 'Micro' otherwise." AI generates the CASE block. Verify the boundary conditions, especially whether the thresholds are inclusive or exclusive, before using it in a view or report.

5. Build NULL-safe calculations with AI guidance.
If you are unsure which columns in your table contain NULLs, ask AI: "Given a table with columns total_amount, discount, tax, and shipping_cost, write a query that calculates the final amount safely, treating any NULL as zero." AI will produce a query using COALESCE for each potentially NULL column. This is faster than manually inspecting every column and guessing which ones might be empty. Test the result with known data points to confirm the calculation behaves correctly.

A habit worth building from this lesson onward: before modifying any production data, always run a SELECT preview, always have a backup or rollback plan, and always ask AI to sanity-check your syntax and logic. Data modification is where mistakes are most expensive. AI is your safety net, but the final responsibility for what gets changed is always yours.

Next lesson: aggregate functions, GROUP BY, and HAVING.