The UPDATE statement is used to update or modify the existing data in the table on a certain condition. In this tutorial, we will be learning the UPDATE statement in PostgreSQL with some other important points related to it. So, let’s get started!
Introduction to UPDATE Statement in PostgreSQL
The UPDATE statement can be used to modify the entire data in the table or some specific records based on a certain condition.
Note that, the UPDATE statement only updates the records in the table and not the table structure. To update the table structure or schema, ALTER statement is used.
Now let’s see the syntax of the UPDATE statement in PostgreSQL.
Syntax of PostgreSQL UPDATE
The following syntax illustrates how the UPDATE statement can be used:
UPDATE tableName
SET col1 = newVal1,
col2 = newVal2,
...
WHERE condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here,
- UPDATE tableName- Specify the table name that you want to update the data of.
- SET col=newVal- The SET keyword modifies all the mentioned column values with the new values. All other columns’ values remain the same.
- WHERE- The WHERE condition is used when you want to target only a particular record or set of records. If you skip the WHERE condition, the UPDATE statement will update the specified values for all the rows in the table.
Whenever the UPDATE statement executes, it returns the number of rows updated.
If you want to display the rows that have been modified, you can use the RETURNING clause at the end of the UPDATE query as shown below:
UPDATE tableName
SET col1 = newVal1,
col2 = newVal2,
...
WHERE condition
RETURNING * | colNamesCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Now let’s see the UPDATE statement in action.
Examples of PostgreSQL UPDATE
To demonstrate the examples, we will be using the table that we have already created. Below is the table data:
Check: How to Create a Table in PostgreSQL?
Update All Records From the Table
As said before, if we skip the WHERE condition, the query will update all the records available in the table.
In our table data, we will update the salary of all employees. Let’s see how can we do that.
UPDATE employees
SET salary= salary+1000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the new salary will be the previous salary+1000. Note that, you can directly assign a new value or use an expression to create a new value from the existing value.
As you can see in the image, the query returns ‘UPDATE 11’, which means all available records have been updated.
Update Particular Records
To target particular records, we use the WHERE condition. In our table, we want to increase the salary of employees having the department ‘engineering’.
UPDATE employees
SET salary= salary+5000
WHERE department='Engineering';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the query has updated three records and increased the salary of employees having only the engineering department.
Returning the Updated Records
Now let’s see the RETURNING keyword that returns the updated records.
UPDATE employees
SET department='HR' WHERE id=6
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In the above query, we are targeting only a single record. Therefore, the RETURNING keyword returns the updated record with the new value.
Note that, you can use any valid condition with the WHERE clause to target the particular records. For example, records between two ids or two values, records that match the LIKE operator expression etc.
Conclusion
The UPDATE statement is pretty straightforward. Note that, if you are uncertain about which records will be updated, first fetch records using the same condition as the UPDATE statement. For example, if you want to update the records having IDs between 1 and 10, then try querying the records using the SELECT statement. This way, even if the condition is complex, you won’t get confused.