The Postgres tables contain data in the form of columns and rows. This data is inserted into the table to use effectively and perform certain operations on the data. There might be some cases where the data we inserted becomes outdated and needs to be updated for use. What should we do in this case?
Postgres provides an UPDATE statement, using which we can update the data entries when needed. In this article, we will see how the user can use the UPDATE statement to update the table values.
PostgreSQL UPDATE – How to Update Table Data?
Updating a Data record in Postgres is possible by utilizing the UPDATE query. This query updates a value to a new value. The basic syntax for the UPDATE statement can be written as:
UPDATE t_name
SET col1 = new_val1,
col2 = new_ val2,
...
WHERE cond;
Syntax Description
- The UPDATE statement updates the value in the table whose name is specified after it.
- The SET keyword sets the values/data to a new/updated value in a table column.
- The values get updated where the data satisfies the condition specified after the WHERE statement.
Let’s see how we can update the values in a table using an example.
Example 1: Updating the Table Data
Consider the table “messages”, which has 4 columns and contains information about the messages. The table data is illustrated below
SELECT * FROM messages;
Now if we want to update the message status of the message with id 3 from “Delivered” to “Read”, we will write the following query:
UPDATE messages
SET msg_status = 'Read'
WHERE msg_id = 3;
The above query says that the msg_status will get updated to “Read” from whatever its value was, where the value of msg_id is 3. This simply means that the status of the message with id 3 will get updated to “Read”. The query will successfully update the table.
Let’s see if the value has been updated in the table or not. To see this we will execute the SELECT query:
We can see that the status of the msg with id 3 has been updated.
If we only want to see the row that is updated, we will run the following query:
SELECT *
FROM messages
WHERE msg_id = 3;
The output of the above query is:
We can update multiple values from the table as well.
Example 2: Updating Multiple Table Values
We can update the multiple values in a similar way as we did in the above case. Let’s update the value of msg_status of a message having id 4 to “Read” and the “sent_to” column value to “Marie”. The query for this can be written as:
UPDATE messages
SET msg_status = 'Read' ,
sent_to = 'Marie'
WHERE msg_id = 4;
This will update the two values of the message with id 4.
Now we will execute the SELECT statement to see the modifications.
SELECT * FROM messages;
We can see that the values of the msg_status and sent_To columns have been updated to the new specified values.
Example 3: Updating All Values of a Specific Column
A user can also update all the values of a specific column in Postgres. The basic purpose served by the WHERE clause was to apply the UPDATE statement where the condition is met. Now if we want to update all the values of a table column by one value, we will drop the WHERE clause. Let’s write the query for updating all the msg_content columns by a string i.e. “Hi, Welcome to Postgres Series”.
UPDATE messages
SET msg_content = 'Hi, Welcome to Postgres Series' ;
The above code will update all the msg_content column values to the string given:
Now to see the table content we will execute the SELECT query like this.
We can see that all the values of the msg_content column have been updated to the new value.
So this is how we can use the UPDATE statement to update the table data.
Conclusion
To update the table values/data in PostgreSQL, the UPDATE command is used. This statement allows the user to update the previous data to new data values. The SET statement is used with the UPDATE statement to assign new values to columns having old values. This blog demonstrated how we can use the UPDATE statement to update the table data with examples.
