PostgreSQL - UPDATE

Quiz

In PostgreSQL, UPDATE statement is used to modify or change the existing records in a table. You can use WHERE clause with UPDATE statement to update the selected rows. Otherwise, all the rows would be updated.

In this tutorial, we will learn how to update the specific data in one or more columns or rows in a table.

Syntax

Following is the syntax of UPDATE statement with WHERE clause is as follows −

UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE [condition];

Here,

You can combine N number of conditions using AND or OR operators.

Example of PostgreSQL UPDATE Statement

Consider the table COMPANY, having records as follows −

testdb# select * from COMPANY;

(7 rows)
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

In this example, we are updating ADDRESS for a customer, whose ID is 3 −

testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

Now, COMPANY table would have the following records −

(7 rows)
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

Previously, ID 3 salary was 20000. Now it's 15000.

If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query would be as follows −

testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

Now, COMPANY table will have the following records −

(7 rows)
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

Print Page

Save or share this article