YouTipPostgresql Update
## PostgreSQL UPDATE Statement
In PostgreSQL, the `UPDATE` statement is used to modify existing records in a table. To prevent updating all rows in your table, it is crucial to use a `WHERE` clause to filter the specific rows you want to modify.
---
### Syntax
The basic syntax of the `UPDATE` query in PostgreSQL is as follows:
```sql
UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE ;
```
#### Key Rules:
* **Multiple Columns:** You can update one or more columns at the same time by separating them with commas.
* **Conditional Filtering:** The `WHERE` clause specifies which row or rows should be updated. If you omit the `WHERE` clause, **all rows** in the table will be updated with the new values.
---
### Sample Data Setup
To demonstrate how the `UPDATE` statement works, let's use a sample table named `COMPANY`.
Assume the `COMPANY` table contains the following records:
```sql
runoobdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(7 rows)
```
---
### Examples
#### Example 1: Updating a Single Row
The following query updates the `salary` of the employee whose `id` is `3`:
```sql
runoobdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
```
After executing the update, querying the table shows that Teddy's salary (ID 3) has been successfully changed to `15000`:
```sql
runoobdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
3 | Teddy | 23 | Norway | 15000
(7 rows)
```
---
#### Example 2: Updating Multiple Columns for All Rows
If you want to update multiple columns simultaneously and apply the changes to the entire table, you can omit the `WHERE` clause.
The following query updates both the `address` and `salary` columns for **all** records in the `COMPANY` table:
```sql
runoobdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000;
```
The updated table will look like this:
```sql
runoobdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+---------+--------
1 | Paul | 32 | Texas | 20000
2 | Allen | 25 | Texas | 20000
4 | Mark | 25 | Texas | 20000
5 | David | 27 | Texas | 20000
6 | Kim | 22 | Texas | 20000
7 | James | 24 | Texas | 20000
3 | Teddy | 23 | Texas | 20000
(7 rows)
```
---
### Important Considerations
1. **The Importance of the `WHERE` Clause:** Always double-check your `WHERE` clause before executing an `UPDATE` statement. Omitting it will overwrite data across the entire table, which can lead to irreversible data loss if not run inside a transaction.
2. **Using Transactions:** For critical updates, wrap your query in a transaction block (`BEGIN;` ... `COMMIT;` or `ROLLBACK;`). This allows you to verify the changes before permanently saving them to the database.
3. **RETURNING Clause:** PostgreSQL supports a unique `RETURNING` clause. Adding `RETURNING *` or `RETURNING column_name` at the end of your `UPDATE` statement allows you to see the modified rows immediately without running a separate `SELECT` query.