PostgreSQL

Postgres Update Rows

PostgreSQL allows you to insert, update or delete values in a column or row through built-in keywords of “INSERT”, “UPDATE”, and “DELETE”. These keywords make it less complicated, time-efficient, and scalable for users to modify their table rows or columns. In this article, we will be talking about how you can update your rows in a table using the “UPDATE” keyword in PostgreSQL. You will learn different implementations of how to update rows in a table and making it more understandable. To update rows, you need to use the “UPDATE” keyword in your statement. The use of the “UPDATE” command is to modify any changes in your table. The basic syntax for updating a row in a table is given below:

UPDATE table_name

SET column1 = value1, ... , columnN = valueN ;

The above statement explains the following:

Mention the “UPDATE” keyword first. Following that, mention the name of the table you want to modify.

Then, write the “SET” keyword along with the columns name “column1”, and set the value you want to enter in place of “Value1”. You can enter more than one column after the “SET” keyword. The “SET” keyword, as the name suggests, will set the value you have inputted for a specific column, and it will keep the values of the columns you have not mentioned as they are.

In the “UPDATE” statement, you can also include the conditions using the “WHERE” clause. It is optional if you want to add the condition, then mention the “WHERE” clause. The syntax for including the “WHERE” condition in the statement is:

UPDATE table_name

SET column1 = value1, ..., columnN = valueN

WHERE conditions ;

Graphical user interface Description automatically generated with medium confidence

It is up to you to add the “WHERE” condition. It will filter all the records and modify only the mentioned ones. If you don’t include the “WHERE” clause, it will update all the records in the table.

Update a Table With the RETURNING Clause:

As the “WHERE” clause in the “UPDATE” statement is optional, it also has the optional “RETURNING” clause. The “RETURNING” clause will return the rows that are modified in the table. The following syntax will demonstrate the example:

UPDATE table_name

SET column1 = value1, ..., columnN = valueN

WHERE conditions

RETURNING * ;

Text Description automatically generated with medium confidence

In the above statement, the “*” returns the records of all the rows that are updated.

Return Record of Specific Row:

If you want to return the information of a specific row run the following query:

UPDATE table_name

SET column1 = value1, ..., columnN = valueN

WHERE conditions

RETURNING AGE ;

Text Description automatically generated with medium confidence

By mentioning a specific column name, you can retrieve the record of that row.

Return Row Using “AS” Keyword:

You can also change the value of the return value you have specified by executing the following statement:

UPDATE table_name

SET column1 = value1, ..., columnN = valueN

WHERE conditions

RETURNING AGE as NUMBERS ;

Text Description automatically generated

The “AS” keyword will return the updated column “AGE” with the different name specified as “NUMBERS”.

PostgreSQL Update Rows Into Table:

For updating any row in a table, there must be a table exist first. For creating a table, the “CREATE” command is used that can be seen in the following demonstration:

CREATE TABLE student_course_info(

"Id" INT PRIMARY KEY NOT NULL ,

"f_name" VARCHAR (100) ,

"l_name" VARCHAR (100) ,

"Age" INTEGER NOT NULL ,

"dept" VARCHAR (255) ,

"course_name" VARCHAR (255) ,

"fees" VARCHAR (255)

)

Text Description automatically generated

The table is created with the name “student_course_info”. I have inserted some data in it, which can be seen below in the results:

Now, we can update the rows in the table above “student_course_info” with “UPDATE” commands.

Update Single Row Into a Table in PostgreSQL:

We will first update a single row into a table in PostgreSQL using the “UPDATE” command, which can be done by the following syntax:

UPDATE "student_course_info"

SET course_name = 'Programming'

WHERE "Id" = 3;

A screenshot of a computer Description automatically generated with medium confidence

After the “UPDATE” command in the above syntax, the table name is specified as “student_course_info” in which we want to make changes. We have modified the “course_name” column of the table and set the value from ‘Statics’ to ‘Programming’ in the row where the Id = 3. The “WHERE” clause in the table is directed to the specific row which we wanted to modify. The above syntax shows the following output:

For validation that the row is updated correctly, display the table using the “SELECT” statement as:

SELECT * FROM "student_course_info"

Graphical user interface Description automatically generated

As we can verify from above that the “course_name” column where Id = 3 is now updated from the value ‘Statics’ to ‘Programming’.

Update Multiple Rows Into a Table in PostgreSQL:

In the above example, we have updated a single row into a table using the “UPDATE” command which returns the entire table in the results. Let’s run a query in this example that updates multiple rows in a table and returns only the columns we have updated:

UPDATE "student_course_info"

SET "course_name" = 'Ethics',

"Age" = 23,

"fees" = '30000'

WHERE "Id" = 2

RETURNING "course_name", "Age", "fees" ;

Text Description automatically generated

The previous statement updates multiple rows in a table that includes “course_name”, “Age”, and “fees”, where the Id = 2 and it returns the rows that we have updated. The result for the above statement can be seen below:

The output above verifies that the columns “course_name”, “Age”, and “fees” are updated successfully with the values I have set and displays the row we have specified in the query using the “RETURNING” clause.

Conclusion:

This article was based on how to update rows in a table using the “UPDATE” command in PostgreSQL. In this guide, we have learned the use of the “UPDATE” command in different queries. The implementation of the “UPDATE” command using the “WHERE” and “RETURNING” clauses in the statements and updating single and multiple rows with returning only the updated values in a table. It is easy to implement “UPDATE” statements in PostgreSQL and time-efficient because there is no need to create a command to update the entire table. It would be complicated to update tables with large amounts of data. We hope you found this article helpful. Continue to follow Linux Hint for more tips and information.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.