UPDATE: Modifying Data

PostgreSQL UPDATE Statement: Modifying Data

Welcome back to our PostgreSQL tutorial series! In this lesson, we'll focus on the UPDATE statement in PostgreSQL, which is essential for modifying existing data in your database tables. Understanding how to use the UPDATE statement effectively allows you to manage and maintain your data with precision.

Introduction to the UPDATE Statement

The UPDATE statement in PostgreSQL is used to change existing data in a table. You can update single rows, multiple rows, or all rows, and you can modify one or multiple columns at once.

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • UPDATE: Specifies the table where the data will be updated.
  • SET: Lists the columns and their new values.
  • WHERE: Defines the condition to identify which rows to update.

Updating Data in PostgreSQL Tables

Let's explore various ways to use the UPDATE statement with practical examples using our familiar library and movies database.

1. Updating a Single Row

Example: Update the price of a specific book

UPDATE books
SET price = 8.99
WHERE title = '1984';

View Schema Initialization

Explanation:

  • The price of the book '1984' is updated to $8.99.
  • The WHERE clause ensures only the row with the title '1984' is affected.

2. Updating Multiple Rows

You can update multiple rows that meet a certain condition.

Example: Increase the price of all books by 'Jane Austen' by $1

UPDATE books
SET price = price + 1
WHERE author = 'Jane Austen';

Explanation:

  • All books authored by 'Jane Austen' have their price increased by $1.

3. Updating Multiple Columns

You can update multiple columns in a single statement.

Example: Update price and pages of a book

UPDATE books
SET price = 12.99, pages = 200
WHERE title = 'The Great Gatsby';

Explanation:

  • The book 'The Great Gatsby' now has a price of $12.99 and 200 pages.

4. Updating All Rows

Omitting the WHERE clause updates all rows in the table.

Example: Apply a 10% discount to all books

UPDATE books
SET price = price * 0.9;

Explanation:

  • All books have their price reduced by 10%.
  • Use caution when updating all rows.

5. Using Subqueries in UPDATE

You can use subqueries to assign values based on data from other tables.

Example: Set the price of books to the average price of all books

UPDATE books
SET price = sub.avg_price
FROM (
    SELECT AVG(price) AS avg_price
    FROM books
) AS sub;

Explanation:

  • Every book's price is updated to the average price of all books.

6. Using CASE Expression

The CASE expression allows conditional logic within the UPDATE statement.

Example: Apply different discounts based on price

UPDATE books
SET price = CASE
    WHEN price > 10 THEN price * 0.85
    ELSE price * 0.95
END;

Explanation:

  • Books priced over $10 get a 15% discount.
  • Books priced $10 or less get a 5% discount.

7. Updating with Subqueries

Since our schema does not include additional tables for joining, we can use subqueries to perform complex updates.

Example: Increase the price of books by authors who have more than one book

UPDATE books
SET price = price + 2
WHERE author IN (
    SELECT author
    FROM books
    GROUP BY author
    HAVING COUNT(*) > 1
);

Explanation:

  • Authors who have written more than one book are identified.
  • Books by these authors have their price increased by $2.

8. Using LIMIT with UPDATE

PostgreSQL allows the use of LIMIT with UPDATE to restrict the number of rows affected.

Example: Update the price of the first 2 books in alphabetical order

UPDATE books
SET price = price - 1
WHERE ctid IN (
    SELECT ctid
    FROM books
    ORDER BY title
    LIMIT 2
);

Explanation:

  • The prices of the first two books alphabetically are reduced by $1.
  • ctid is a system column that uniquely identifies a row in a table.

9. Returning Updated Data

The RETURNING clause allows you to return data from the updated rows.

Example: Update pages and return updated books

UPDATE books
SET pages = pages + 10
RETURNING title, pages;

Explanation:

  • All books have 10 pages added.
  • The updated title and pages are returned.

10. Conditional Updates with EXISTS

Use EXISTS to conditionally update rows based on the existence of related data.

Example: Update books only if the author exists in the authors table

UPDATE books
SET price = price * 0.95
WHERE EXISTS (
    SELECT 1
    FROM authors
    WHERE authors.name = books.author
);

Explanation:

  • Books are discounted only if their author is listed in the authors table.
  • In our schema, all authors in the books table exist in the authors table, so this will update all books.

Conclusion

The UPDATE statement in PostgreSQL is a powerful tool for modifying existing data. By mastering its various features, you can efficiently manage and maintain the data within your database.

Key Takeaways:

  • Use UPDATE ... SET to modify data.
  • Always use a WHERE clause to specify which rows to update.
  • Utilize subqueries and CASE expressions for complex updates.
  • Use RETURNING to retrieve information about updated rows.
  • Be cautious when updating all rows or using operations that affect multiple rows.

Schema Initialization

To run the examples provided, ensure your PostgreSQL database is set up with the following schema:

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('Sense and Sensibility', 'Jane Austen', 5.99, 226),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

Feel free to experiment with the UPDATE statement using this schema to enhance your understanding of how to modify data in PostgreSQL. Happy coding!

INSERT: Adding New Data