Updating Existing Records: Modifying Data in PostgreSQL

Previously in this course, we covered Inserting Records into Tables: Data Population in PostgreSQL. Now that you have populated your store database with products and customer profiles, you inevitably need a way to change that data. Whether it's correcting a typo, updating a product price, or changing a customer's email address, data modification is a daily task for any database practitioner.

In this lesson, we will use the UPDATE statement to modify existing information in our database, ensuring our records remain accurate and current.

Understanding the UPDATE Statement

The UPDATE statement is the primary tool for modifying existing rows in a SQL table. Unlike INSERT, which creates new data, UPDATE replaces the current values in a row with new ones.

The syntax follows this structure:

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

The most critical component here is the WHERE clause. If you omit the WHERE clause, PostgreSQL will update every single row in the table. In a production environment, this is almost always catastrophic. Always write your WHERE clause before you write your SET clause to avoid accidental mass updates.

Updating Individual Records

Let's apply this to our store project. Imagine we have a product in our products table that needs a price adjustment.

First, identify the row you want to change:

SQL
SELECT * FROM products WHERE product_id = 5;

Once you have confirmed the record, perform the update:

SQL
UPDATE products
SET price = 19.99
WHERE product_id = 5;

PostgreSQL will return a message like UPDATE 1, indicating that one row was successfully modified.

Updating Multiple Columns

Often, a single event triggers changes in multiple attributes. For example, if a product changes its name and its stock level simultaneously, you can handle both in one statement.

SQL
UPDATE products
SET product_name = 'Mechanical Keyboard - RGB',
    stock_quantity = 50
WHERE product_id = 12;

By separating columns with commas, you can update as many fields as necessary in a single command. This is more efficient than executing multiple separate UPDATE statements, as it performs the changes in a single operation.

Hands-on Exercise

Using our store database, perform the following tasks:

1. Find the customer with customer_id = 1.
2. Update their email address to a new value (e.g., 'new_email@example.com').
3. In a single query, update both their last_name and phone_number.
4. Verify the changes using a SELECT query.

Common Pitfalls

Even experienced engineers occasionally make mistakes with data modification. Keep these guardrails in mind:

• Forgetting the WHERE clause: As mentioned, executing UPDATE products SET price = 0; will set every product in your store to free. Always double-check your condition.
• Assuming the data state: Don't assume you know what is in a row before updating it. Always run a SELECT query first to confirm the target ID matches the record you intend to change.
• Data Type Mismatches: If you attempt to update a price column (numeric) with a text string like 'twenty dollars', PostgreSQL will throw an error and reject the update. Ensure your data types align with your designing the products table schema.
• Violating Constraints: If you try to update a value to something that violates a constraint, such as a using CHECK constraints in PostgreSQL that prevents negative prices, the UPDATE will fail.

Frequently Asked Questions

Q: Can I undo an UPDATE if I make a mistake? A: If you are working within an open transaction, you can use ROLLBACK. We will cover this in detail when we discuss transactions later in the course.

Q: How do I update a column based on its current value? A: You can reference the current column value in the SET clause. For example: UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 5;

Q: Does updating a record change its Primary Key? A: You can update a primary key, but it is generally discouraged because it can break relationships with other tables (foreign keys).

Recap

You now know how to use the UPDATE statement to modify existing records in your PostgreSQL database. You've learned the importance of the WHERE clause for targeting specific rows and how to update multiple columns in one transaction to maintain data consistency.

Up next: Deleting Records Safely — where we will learn how to remove data that is no longer needed while maintaining the integrity of our store schema.