✏️ Updating & Deleting

Beyond reading data, you'll often need to modify and remove records from your database. The UPDATE and DELETE statements are essential for data management, but they must be used carefully to avoid unintended data loss.

🔄 The UPDATE Statement

UPDATE modifies existing data in a table. Always use a WHERE clause to specify which records to update.

Syntax:

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

WARNING: Always test your WHERE clause with a SELECT first!

Example 1: Updating a Single Record

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Update a customer's email
customer_id = 42
new_email = "newemail@example.com"

cursor.execute("""
    UPDATE customers
    SET email = ?
    WHERE customer_id = ?
""", (new_email, customer_id))

conn.commit()
print(f"Updated customer {customer_id}")

conn.close()

Example 2: Updating Multiple Columns

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Update book information and restock date
book_id = 15
new_price = 19.99
new_quantity = 50

cursor.execute("""
    UPDATE books
    SET price = ?, quantity_in_stock = ?, last_restocked = CURRENT_TIMESTAMP
    WHERE book_id = ?
""", (new_price, new_quantity, book_id))

conn.commit()
print(f"Updated book {book_id}: price=${new_price}, stock={new_quantity}")

conn.close()

Example 3: Updating Multiple Records with WHERE

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Apply a 10% discount to all fiction books
cursor.execute("""
    SELECT COUNT(*) FROM books WHERE genre = 'Fiction'
""")
count = cursor.fetchone()[0]
print(f"Found {count} fiction books to discount")

# Test first with SELECT
cursor.execute("""
    SELECT title, price
    FROM books
    WHERE genre = 'Fiction'
    LIMIT 3
""")
print("Sample of books being updated:")
for title, price in cursor.fetchall():
    print(f"  {title}: ${price:.2f}")

# Now perform the update
cursor.execute("""
    UPDATE books
    SET price = price * 0.9
    WHERE genre = 'Fiction'
""")

conn.commit()
print(f"Applied 10% discount to {cursor.rowcount} fiction books")

conn.close()

🗑️ The DELETE Statement

DELETE removes records from a table. Like UPDATE, always use WHERE to specify which records to delete.

Syntax:

DELETE FROM table_name
WHERE condition;

CAUTION: DELETE without a WHERE clause deletes ALL records!

Example 4: Deleting a Single Record

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Delete a specific review
review_id = 99

# First, verify the record exists
cursor.execute("SELECT * FROM reviews WHERE review_id = ?", (review_id,))
if cursor.fetchone():
    cursor.execute("DELETE FROM reviews WHERE review_id = ?", (review_id,))
    conn.commit()
    print(f"Deleted review {review_id}")
else:
    print(f"Review {review_id} not found")

conn.close()

Example 5: Safe Deletion with Verification

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Delete all orders for an inactive customer (be very careful!)
customer_id = 77

# Step 1: Verify the records to be deleted
cursor.execute("SELECT COUNT(*) FROM orders WHERE customer_id = ?", (customer_id,))
order_count = cursor.fetchone()[0]
print(f"Found {order_count} orders for customer {customer_id}")

# Step 2: Show a sample
cursor.execute("""
    SELECT order_id, order_date, amount
    FROM orders
    WHERE customer_id = ?
    LIMIT 3
""", (customer_id,))
print("Sample of orders to be deleted:")
for order_id, date, amount in cursor.fetchall():
    print(f"  Order {order_id} ({date}): ${amount:.2f}")

# Step 3: Ask for confirmation (in real code, prompt user)
confirm = True  # Set to False to skip deletion

if confirm and order_count > 0:
    cursor.execute("DELETE FROM orders WHERE customer_id = ?", (customer_id,))
    conn.commit()
    print(f"Deleted {cursor.rowcount} orders")
else:
    print("Deletion cancelled")

conn.close()

🔐 Data Integrity Best Practices

Best Practices for Safe Modifications:

1. Always use WHERE clauses - Never update/delete everything

2. Test with SELECT first - Verify you're targeting the right records

3. Use transactions - Allows rollback if something goes wrong

4. Back up data - Before bulk operations

5. Use parameterized queries - Prevents SQL injection

Example 6: Using Transactions for Safety

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

try:
    # Start transaction (implicit with SQLite)
    
    # Update inventory
    cursor.execute("""
        UPDATE books
        SET quantity_in_stock = quantity_in_stock - ?
        WHERE book_id = ? AND quantity_in_stock >= ?
    """, (5, 42, 5))
    
    if cursor.rowcount == 0:
        raise Exception("Insufficient stock!")
    
    # Create order record
    cursor.execute("""
        INSERT INTO orders (customer_id, book_id, quantity, order_date)
        VALUES (?, ?, ?, CURRENT_TIMESTAMP)
    """, (10, 42, 5))
    
    # Commit only if both succeeded
    conn.commit()
    print("Order processed successfully")

except Exception as e:
    # Rollback on any error
    conn.rollback()
    print(f"Error: {e}. Order cancelled and inventory rolled back.")

finally:
    conn.close()

Example 7: Conditional Updates (IF EXISTS pattern)

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Update price only if current price is below a threshold
book_id = 25
new_price = 17.99

cursor.execute("""
    UPDATE books
    SET price = ?
    WHERE book_id = ? AND price < 15
""", (new_price, book_id))

if cursor.rowcount > 0:
    conn.commit()
    print(f"Updated price for book {book_id}")
else:
    print(f"Book {book_id} price is already >= $15, no update needed")

conn.close()

⚙️ Handling Update/Delete Errors

Example 8: Error Handling

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

try:
    # Attempt to delete a book (may fail due to foreign key constraints)
    book_id = 55
    
    cursor.execute("DELETE FROM books WHERE book_id = ?", (book_id,))
    conn.commit()
    print(f"Book {book_id} deleted successfully")

except sqlite3.IntegrityError as e:
    print(f"Cannot delete book {book_id}: {e}")
    print("Possibly because it has active orders")
    conn.rollback()

except Exception as e:
    print(f"Unexpected error: {e}")
    conn.rollback()

finally:
    conn.close()

🔑 Key Takeaways

• **UPDATE** modifies existing records; always use a WHERE clause

• **DELETE** removes records; always verify before deleting

• **Always test WHERE clauses** with SELECT statements first

• **Use transactions** to ensure data consistency

• **Use parameterized queries** to prevent SQL injection

• **Check rowcount** after updates/deletes to confirm changes

• **Handle errors gracefully** with try-except blocks

• **Back up important data** before bulk operations

📚 Further Learning

Ready to learn more advanced modification techniques?

• [Advanced Updates & Deletes](/courses/python/database_basics/advanced/updating_deleting) - Cascading deletes and soft deletes

• [Joins & Relations](/courses/python/database_basics/beginner/joins_and_relations) - Understanding data relationships

• [Challenges & Quizzes](/#) - Practice safe data modification