Modifying Existing Data

You've learned how to create data with INSERT. Now it's time to master modifying and removing data with UPDATE and DELETE. These are powerful operations that change your database permanently – and that power comes with responsibility.

In test automation, you'll use these statements constantly: updating order statuses to simulate workflow progression, modifying user attributes to test different scenarios, deleting test data between test runs, or cleaning up after failed tests.

But here's the critical truth about UPDATE and DELETE: one missing WHERE clause can destroy an entire table of data. There's no undo button in SQL. Once you execute DELETE FROM Users without a WHERE clause, every user is gone – instantly and permanently (unless you're in a transaction, which you'll learn about in the next lesson).

This lesson will teach you not just the syntax, but the safety practices that prevent catastrophic mistakes.

UPDATE: Changing Existing Rows

The UPDATE statement modifies one or more columns in existing rows. The basic syntax:

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

Here's a simple example that marks an order as shipped:

UPDATE Orders
SET Status = 'Shipped',
    ShipmentDate = CURRENT_TIMESTAMP
WHERE OrderId = 12345;

This updates two columns (Status and ShipmentDate) for one specific order. The WHERE clause ensures only order 12345 is affected.

You can update multiple rows at once by using a broader WHERE condition:

UPDATE Orders
SET Status = 'Cancelled'
WHERE Status = 'Pending'
  AND OrderDate < CURRENT_DATE - INTERVAL '30 days';

This cancels all pending orders older than 30 days. The database processes each matching row individually, applying the same change to all of them.

The WHERE clause determines which rows get updated. Without it, every row in the table gets modified.

UPDATE with Calculations

You can use calculations and expressions in UPDATE statements, including referencing the column's current value:

-- Apply a 10% price increase to all products in Category 3
UPDATE Products
SET Price = Price * 1.10
WHERE CategoryId = 3;

The expression Price = Price * 1.10 reads the current price, multiplies it by 1.10, then stores the result back. For a product currently priced at $50, this would update it to $55.

You can combine multiple column updates with different calculations:

-- Process an order: reduce stock and update status
UPDATE Products
SET StockQuantity = StockQuantity - 5,
    LastSoldDate = CURRENT_TIMESTAMP
WHERE ProductId = 789;

Here's a more complex example using CASE for conditional updates:

-- Apply category-specific price adjustments
UPDATE Products
SET Price = CASE
    WHEN CategoryId = 1 THEN Price * 1.15  -- 15% increase for electronics
    WHEN CategoryId = 2 THEN Price * 1.05  -- 5% increase for clothing
    ELSE Price * 1.08  -- 8% increase for everything else
END
WHERE IsActive = 1;

This applies different percentage increases based on each product's category, but only to active products.

Calculations in UPDATE statements execute once per affected row, using that row's current values.

UPDATE with Subqueries

You can use subqueries in UPDATE statements both in the SET clause (to calculate new values) and in the WHERE clause (to determine which rows to update):

-- Mark users as inactive if they haven't ordered in 6 months
UPDATE Users
SET IsActive = 0,
    DeactivatedDate = CURRENT_TIMESTAMP
WHERE UserId IN (
    SELECT u.UserId
    FROM Users u
    LEFT JOIN Orders o ON u.UserId = o.UserId
    WHERE o.OrderId IS NULL
       OR MAX(o.OrderDate) < CURRENT_DATE - INTERVAL '180 days'
);

You can also use subqueries to set values based on calculations from other tables:

-- Update user's total spending based on their order history
UPDATE Users
SET TotalSpent = (
    SELECT COALESCE(SUM(TotalAmount), 0)
    FROM Orders
    WHERE Orders.UserId = Users.UserId
      AND Status = 'Completed'
)
WHERE UserId IN (SELECT DISTINCT UserId FROM Orders);

This calculates each user's total spending from completed orders and stores it in the Users table. The subquery in the SET clause is correlated – it references Users.UserId from the outer query, so it calculates a different sum for each user being updated.

Correlated subqueries in UPDATE statements can be slower on large datasets, but they're valuable when you need to calculate values based on related data.

UPDATE with JOIN (Database-Specific Syntax)

Some databases allow you to use JOIN syntax in UPDATE statements to update rows based on data from other tables. The syntax varies by database:

SQL Server syntax:

-- Reduce stock quantity for all items in a specific order
UPDATE p
SET p.StockQuantity = p.StockQuantity - oi.Quantity
FROM Products p
INNER JOIN OrderItems oi ON p.ProductId = oi.ProductId
WHERE oi.OrderId = 12345;

MySQL syntax:

-- Same operation in MySQL
UPDATE Products p
INNER JOIN OrderItems oi ON p.ProductId = oi.ProductId
SET p.StockQuantity = p.StockQuantity - oi.Quantity
WHERE oi.OrderId = 12345;

PostgreSQL syntax:

-- PostgreSQL uses FROM clause
UPDATE Products
SET StockQuantity = Products.StockQuantity - OrderItems.Quantity
FROM OrderItems
WHERE Products.ProductId = OrderItems.ProductId
  AND OrderItems.OrderId = 12345;

If you need database-agnostic code, use subqueries instead of joins in UPDATE statements – they work consistently across all databases.

Understanding your database's UPDATE with JOIN syntax can make complex updates more efficient, but be aware of portability issues if you switch databases.

DELETE: Removing Rows

The DELETE statement removes rows from a table. The basic syntax:

DELETE FROM table_name
WHERE condition;

Here's a simple example removing test users:

DELETE FROM Users
WHERE Email LIKE 'test_%@example.com';

This removes all users whose email addresses match the pattern test_*@example.com – a common convention for test accounts.

You can use any valid WHERE condition:

-- Delete old pending orders
DELETE FROM Orders
WHERE Status = 'Pending'
  AND OrderDate < CURRENT_DATE - INTERVAL '90 days';

-- Delete inactive users with no order history
DELETE FROM Users
WHERE IsActive = 0
  AND UserId NOT IN (SELECT DISTINCT UserId FROM Orders);

Unlike UPDATE, which modifies rows, DELETE completely removes them from the table. There's no "deleted" flag or soft delete here – the rows are permanently gone.

The WHERE clause is optional, but omitting it is extremely dangerous – it deletes every row in the table.

DELETE with Subqueries and Joins

Just like UPDATE, you can use subqueries in DELETE statements to identify which rows to remove:

-- Delete orders for inactive users
DELETE FROM Orders
WHERE UserId IN (
    SELECT UserId
    FROM Users
    WHERE IsActive = 0
);

Here's a more complex example that uses multiple subqueries:

-- Delete old test data: orders from test users older than 30 days
DELETE FROM Orders
WHERE UserId IN (
    SELECT UserId
    FROM Users
    WHERE Email LIKE '%@testcompany.com'
)
AND OrderDate < CURRENT_DATE - INTERVAL '30 days';

Some databases support DELETE with JOIN syntax (similar to UPDATE), but the syntax varies and it's not standard SQL. For portability, stick with subqueries in the WHERE clause:

-- Delete order items for cancelled orders (using subquery - works everywhere)
DELETE FROM OrderItems
WHERE OrderId IN (
    SELECT OrderId
    FROM Orders
    WHERE Status = 'Cancelled'
);

When deleting rows that have relationships to other tables, pay attention to foreign key constraints – you might need to delete child records before parent records.

TRUNCATE: Fast Table Clearing

If you want to delete all rows from a table, TRUNCATE is faster than DELETE:

TRUNCATE TABLE TestResults;

This removes every row from TestResults – instantly. No WHERE clause, no conditions, no logging individual row deletions. The table structure remains intact, but all data is gone.

TRUNCATE vs DELETE: Key Differences

TRUNCATE:

• Removes all rows (you can't use WHERE)
• Much faster – doesn't log individual row deletions
• Resets auto-increment counters (next insert starts from 1)
• Cannot be rolled back in some databases (even inside transactions)
• Requires higher permissions than DELETE
• Doesn't fire row-level triggers

DELETE:

• Can selectively remove rows with WHERE
• Slower – logs each row deletion
• Doesn't reset auto-increment counters
• Can be rolled back in transactions
• Works with standard permissions
• Fires row-level triggers

Use TRUNCATE when:

• You need to clear an entire table quickly
• You want to reset auto-increment counters
• You're clearing staging or test tables between runs

Use DELETE when:

• You need to remove specific rows (with WHERE)
• You need the operation to be transactional and rollback-safe
• You need triggers to fire

In test automation, TRUNCATE is perfect for quickly resetting tables to a clean state between test runs.

CASCADE Deletes and Foreign Keys

When tables have foreign key relationships, deleting a parent row might fail if child rows still reference it. For example, you can't delete a user who has orders (because Orders.UserId references Users.UserId) unless you handle the child records first.

You have three options:

Option 1: Delete child records first

-- Delete orders for a user, then delete the user
DELETE FROM Orders WHERE UserId = 123;
DELETE FROM Users WHERE UserId = 123;

Option 2: Use CASCADE constraints (defined at schema level)

If the foreign key was created with ON DELETE CASCADE, deleting the parent automatically deletes all child records:

-- If Orders.UserId has ON DELETE CASCADE, this deletes the user AND all their orders
DELETE FROM Users WHERE UserId = 123;

This is powerful but dangerous – you might unintentionally delete far more data than intended. Always verify cascade behavior before using it.

Option 3: Soft delete (application pattern)

Instead of physically deleting rows, mark them as deleted with a flag (provided you have one):

-- Soft delete: mark as deleted without removing the row
UPDATE Users
SET IsDeleted = 1,
    DeletedDate = CURRENT_TIMESTAMP
WHERE UserId = 123;

Soft deletes preserve referential integrity and allow "undeleting" data. They require updating your queries to filter out deleted rows:

SELECT * FROM Users WHERE IsDeleted = 0;  -- Only show non-deleted users

In test environments, you typically don't need soft deletes – hard deletes with proper cleanup logic are simpler and more performant.

Essential Safety Practices

This is the most important section of this lesson. Following these practices will save you from catastrophic data loss:

Rule 1: Always Test with SELECT First

Before running UPDATE or DELETE, convert it to a SELECT to preview which rows will be affected:

-- Step 1: Preview with SELECT
SELECT * FROM Users WHERE Email LIKE 'test_%@example.com';

-- Step 2: Verify the results look correct

-- Step 3: Only then execute the DELETE
DELETE FROM Users WHERE Email LIKE 'test_%@example.com';

This pattern catches mistakes before they cause damage. If your SELECT returns 50,000 rows when you expected 5, you know something's wrong with your WHERE clause.

Rule 2: Use Transactions for Safety

Wrap potentially destructive operations in transactions so you can rollback if something goes wrong:

BEGIN TRANSACTION;

    DELETE FROM Orders WHERE UserId = 123;

    -- Check if the right number of rows were affected
    -- If anything looks wrong, ROLLBACK instead of COMMIT

COMMIT;  -- Or ROLLBACK if results aren't as expected

You'll learn more about transactions in the next lesson, but even now, understand that they provide a safety net.

Rule 3: Check Row Counts

After UPDATE or DELETE, check how many rows were affected. Most database tools and libraries report this:

// In C# with Dapper
int rowsAffected = connection.Execute(
    "DELETE FROM Orders WHERE Status = @Status",
    new { Status = "Cancelled" });

if (rowsAffected > 1000)
{
    throw new Exception($"Unexpected: deleted {rowsAffected} rows!");
}

If you expected to delete 10 rows but 10,000 were affected, you've caught a mistake before committing the transaction.

Rule 4: Never Omit WHERE (Unless You Mean It)

An UPDATE or DELETE without a WHERE clause affects every row. If you truly intend this, add a comment explaining why:

-- Intentionally resetting all user passwords for security audit
-- Confirmed with product owner on 2024-11-04
UPDATE Users
SET PasswordHash = NULL, PasswordResetRequired = 1;

But 99% of the time, a missing WHERE is a catastrophic mistake.

Rule 5: Backup Before Bulk Operations

Before running large-scale updates or deletes, especially in production or staging environments, take a backup or snapshot. If something goes wrong, you can restore rather than trying to manually reconstruct lost data.

These safety practices aren't paranoia – they're professional discipline learned from painful experience.