PHP Fundamentals

SQL: Updating & Deleting Data

13 min Lesson 33 of 45

Updating Data with UPDATE

The UPDATE statement modifies existing records in a table. Be very careful with UPDATE - without a proper WHERE clause, it will update ALL rows!

Basic Syntax

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

Critical Warning

ALWAYS include a WHERE clause unless you intentionally want to update all rows. Without WHERE, every single row in the table will be updated!

-- DANGEROUS: Updates ALL products!
UPDATE products SET price = 0;

-- SAFE: Updates only one specific product
UPDATE products SET price = 99.99 WHERE id = 5;

Example: Update a Single Column

-- Update user email
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 10;

Example: Update Multiple Columns

-- Update product price and stock
UPDATE products
SET price = 149.99, stock = 25
WHERE id = 3;

Example: Update Based on Condition

-- Give 10% discount to all Electronics
UPDATE products
SET price = price * 0.9
WHERE category = 'Electronics';

-- Mark all out-of-stock products as inactive
UPDATE products
SET is_active = 0
WHERE stock = 0;

Update with Calculations

-- Increase all prices by 5%
UPDATE products
SET price = price * 1.05
WHERE category = 'Electronics';

-- Add 10 units to stock
UPDATE products
SET stock = stock + 10
WHERE id = 15;

-- Increment view count
UPDATE posts
SET views = views + 1
WHERE id = 42;

Update Multiple Rows

-- Update all products in specific categories
UPDATE products
SET is_active = 1
WHERE category IN ('Electronics', 'Books', 'Furniture');

-- Mark old orders as archived
UPDATE orders
SET status = 'archived'
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

Update with CASE Statement

Apply different updates based on conditions:

-- Apply different discounts based on price range
UPDATE products
SET price = CASE
    WHEN price > 500 THEN price * 0.80    -- 20% off expensive items
    WHEN price > 100 THEN price * 0.90    -- 10% off mid-range
    ELSE price * 0.95                      -- 5% off cheaper items
END
WHERE category = 'Electronics';

Update with LIMIT

-- Update only first 10 matching rows
UPDATE products
SET is_featured = 1
WHERE category = 'Electronics'
ORDER BY price DESC
LIMIT 10;

Checking Updates Before Executing

Best Practice: Test with SELECT First

Before running an UPDATE, run a SELECT with the same WHERE clause to see which rows will be affected:

-- 1. First, check which rows will be affected
SELECT * FROM products
WHERE category = 'Electronics' AND price < 50;

-- 2. If the results look correct, run the update
UPDATE products
SET is_active = 0
WHERE category = 'Electronics' AND price < 50;

-- 3. Verify the update
SELECT * FROM products
WHERE category = 'Electronics' AND price < 50;

Deleting Data with DELETE

The DELETE statement removes rows from a table. Like UPDATE, it's dangerous without a WHERE clause!

Basic Syntax

DELETE FROM table_name
WHERE condition;

Extreme Caution Required!

NEVER forget the WHERE clause unless you want to delete ALL rows!

-- CATASTROPHIC: Deletes ALL users!
DELETE FROM users;

-- SAFE: Deletes one specific user
DELETE FROM users WHERE id = 10;

Example: Delete a Single Row

-- Delete specific product
DELETE FROM products WHERE id = 5;

-- Delete specific user
DELETE FROM users WHERE username = 'spam_account';

Example: Delete Multiple Rows

-- Delete all inactive products
DELETE FROM products WHERE is_active = 0;

-- Delete old orders
DELETE FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- Delete users who never logged in
DELETE FROM users
WHERE last_login IS NULL
AND created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH);

Delete with Multiple Conditions

-- Delete cancelled orders older than 90 days
DELETE FROM orders
WHERE status = 'cancelled'
AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Delete spam comments
DELETE FROM comments
WHERE (content LIKE '%viagra%' OR content LIKE '%casino%')
AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

Delete with LIMIT

-- Delete oldest 100 log entries
DELETE FROM logs
ORDER BY created_at ASC
LIMIT 100;

Testing Deletes Safely

Always Test with SELECT First

-- 1. Preview what will be deleted
SELECT * FROM products WHERE is_active = 0;

-- 2. Check the count
SELECT COUNT(*) FROM products WHERE is_active = 0;

-- 3. If correct, proceed with delete
DELETE FROM products WHERE is_active = 0;

-- 4. Verify deletion
SELECT COUNT(*) FROM products WHERE is_active = 0;  -- Should return 0

Soft Delete vs Hard Delete

Hard Delete (Permanent)

Actually removes rows from the database. Data is gone forever.

DELETE FROM users WHERE id = 10;

Soft Delete (Recommended for Important Data)

Mark rows as deleted instead of actually removing them. This allows recovery and maintains referential integrity.

-- Add a deleted_at column to your table
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- "Delete" by setting timestamp
UPDATE users
SET deleted_at = NOW()
WHERE id = 10;

-- Exclude "deleted" records in queries
SELECT * FROM users WHERE deleted_at IS NULL;

-- Restore a soft-deleted record
UPDATE users
SET deleted_at = NULL
WHERE id = 10;

Soft Delete Benefits

Recoverable: Can restore accidentally deleted data
Audit Trail: Keep history of who deleted what and when
Referential Integrity: Foreign key relationships remain intact
Analytics: Can analyze deleted data patterns

Use soft deletes for: User accounts, orders, posts, important business records

Use hard deletes for: Logs, temporary data, spam, old session data

TRUNCATE vs DELETE

DELETE

-- Deletes rows one by one
-- Can use WHERE clause
-- Triggers are executed
-- Slower for large tables
-- Can be rolled back (in transactions)
DELETE FROM logs WHERE created_at < '2023-01-01';

TRUNCATE

-- Removes all rows at once
-- No WHERE clause allowed
-- Much faster
-- Resets AUTO_INCREMENT counter
-- Cannot be rolled back
TRUNCATE TABLE logs;

TRUNCATE Warning: TRUNCATE cannot be undone and deletes ALL data. Use only when you're absolutely sure!

Cascading Deletes

When you set up foreign keys with ON DELETE CASCADE, deleting a parent row automatically deletes related child rows.

-- Table setup with CASCADE
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Deleting a user automatically deletes their orders and order items
DELETE FROM users WHERE id = 10;
-- This also deletes:
-- - All orders by user 10
-- - All order_items for those orders

Other Foreign Key Options

ON DELETE CASCADE: Delete child rows automatically
ON DELETE SET NULL: Set foreign key to NULL (column must allow NULL)
ON DELETE RESTRICT: Prevent deletion if child rows exist (default)
ON DELETE NO ACTION: Same as RESTRICT

Affected Rows

After UPDATE or DELETE, check how many rows were affected:

-- In MySQL CLI
UPDATE products SET price = 99.99 WHERE id = 5;
-- Shows: Query OK, 1 row affected

-- In PHP (mysqli)
$result = $mysqli->query("UPDATE products SET price = 99.99 WHERE id = 5");
$affected = $mysqli->affected_rows;
echo "Updated $affected rows";

-- In PHP (PDO)
$stmt = $pdo->prepare("UPDATE products SET price = ? WHERE id = ?");
$stmt->execute([99.99, 5]);
$affected = $stmt->rowCount();
echo "Updated $affected rows";

Transaction Safety

For critical updates/deletes, use transactions to ensure data consistency:

-- Start transaction
START TRANSACTION;

-- Perform updates
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If everything looks good, commit
COMMIT;

-- If something went wrong, rollback
ROLLBACK;

When to Use Transactions:

Transferring money between accounts
Processing orders (inventory, payment, order record)
Deleting related records across multiple tables
Any operation where partial completion would be problematic

Practical Examples

Example 1: User Profile Update

-- Update user profile information
UPDATE users
SET
    full_name = 'John Smith',
    phone = '+1-555-0123',
    address = '123 Main St',
    updated_at = NOW()
WHERE id = 25;

Example 2: Order Status Management

-- Mark order as shipped
UPDATE orders
SET
    status = 'shipped',
    shipped_at = NOW(),
    tracking_number = 'TRACK12345'
WHERE id = 100;

-- Cancel pending orders older than 30 days
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

Example 3: Inventory Management

-- Reduce stock after purchase
UPDATE products
SET stock = stock - 2
WHERE id = 15 AND stock >= 2;

-- Restock products
UPDATE products
SET stock = stock + 50, is_active = 1
WHERE id IN (10, 15, 20, 25);

Example 4: Data Cleanup

-- Delete unverified accounts older than 7 days
DELETE FROM users
WHERE email_verified = 0
AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Delete old session data
DELETE FROM sessions
WHERE last_activity < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 HOUR));

-- Archive and delete old logs
INSERT INTO archived_logs SELECT * FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

Exercise: Data Management Operations

Write SQL statements for the following scenarios:

Update product #5 to have a price of $79.99 and stock of 50
Give a 15% discount to all products in the "Books" category
Mark all orders with status "delivered" from 2023 as "completed"
Delete all comments that contain the word "spam"
Soft delete (set deleted_at) for user with email "test@example.com"
Update the view count for blog post #42 by incrementing it by 1
Delete all products that have been inactive for more than 1 year
Update all users who registered before 2020 to have a "legacy_user" flag set to true

Remember: Write SELECT queries first to preview the affected rows!

Best Practices Summary

UPDATE/DELETE Safety Checklist

ALWAYS use WHERE: Unless you intentionally want to affect all rows
Test with SELECT first: Preview which rows will be affected
Backup before bulk operations: Create database backup for large updates/deletes
Use LIMIT: Test on a few rows first with LIMIT
Check affected rows: Verify the expected number of rows changed
Use transactions: For critical multi-step operations
Consider soft deletes: For important data that might need recovery
Add timestamps: Track when records were updated (updated_at column)
Log critical changes: Keep an audit trail of important modifications
Use foreign key constraints: Maintain referential integrity

What's Next?

In the next lesson, you'll learn advanced query techniques including GROUP BY, HAVING, subqueries, and aggregate functions to perform complex data analysis.