PostgreSQL: UPDATE Statement
This PostgreSQL tutorial explains how to use the PostgreSQL UPDATE statement with syntax and examples.
Description
The PostgreSQL UPDATE statement is used to update existing records in a table in a PostgreSQL database.
Syntax
The syntax for the UPDATE statement when updating one table in PostgreSQL is:
UPDATE table SET column1 = expression1 | DEFAULT, column2 = expression2 | DEFAULT, ... [WHERE conditions];
Parameters or Arguments
- column1, column2
- The columns that you wish to update.
- expression1, expression2
- The new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
- DEFAULT
- The default value for that particular column in the table. If no default value has been set for the column, the column will be set to NULL.
- WHERE conditions
- Optional. The conditions that must be met for the update to execute. If no conditions are provided, then all records from the table will be updated.
Example - Update single column
Let's look at a very simple PostgreSQL UPDATE query example.
UPDATE contacts SET first_name = 'Jane' WHERE contact_id = 35;
This PostgreSQL UPDATE example would update the first_name to 'Jane' in the contacts table where the contact_id is 35.
You could also use the DEFAULT keyword to set a column to its default value.
For example,
UPDATE contacts SET first_name = DEFAULT WHERE contact_id = 35;
This PostgreSQL UPDATE example would update the first_name to the default value for the field in the contacts table where the contact_id is 35. If no default value has been set for the first_name column in the contacts table, the first_name column will be set to NULL.
Example - Update multiple columns
Let's look at a PostgreSQL UPDATE example where you might want to update more than one column with a single UPDATE statement.
UPDATE contacts SET city = 'Miami', state = 'Florida' WHERE contact_id >= 200;
When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.
This PostgreSQL UPDATE example would update the city to 'Miami' and the state to 'Florida' where the contact_id is greater than or equal to 200.
Advertisements