The PostgreSQL UPDATE statement is used to modify existing data in a table by changing the values of one or more columns.
- The WHERE clause specifies which rows should be updated.
- Without the WHERE clause, all rows in the table are updated.
- Multiple columns can be updated in a single statement.
Syntax
UPDATE table_name
SET column_1 = value_1,
column_2 = value_2,
...
WHERE condition;
Where:
- table_name: The name of the table to be updated.
- SET: Specifies the column(s) and their new values.
- WHERE: Filters the rows to be updated.
Working
Firstly, create a books table and insert some records into it.
Example 1: Update a Single Column
The following query updates the price of the book with book_id = 2.
Query:
UPDATE books
SET price = 900
WHERE book_id = 2;
SELECT * FROM books;
Output:
Example 2: Update Multiple Columns
The following query updates both the price and stock of the book with book_id = 3.
Query:
UPDATE books
SET price = 550,
stock = 30
WHERE book_id = 3;
SELECT * FROM books;
Output:
Example 3: Update Multiple Rows
The following query increases the stock of all books priced below 700 by 10.
Query:
UPDATE books
SET stock = stock + 10
WHERE price < 700;
SELECT * FROM books;
Output:
Example 4: Omitting the WHERE Clause
The following query updates the author name for all books.
Query:
UPDATE books
SET author = 'Unknown';
SELECT * FROM books;
Output: