PostgreSQL UPDATE Statement

If we want to update data in the PostgreSQL database, we can useUPDATEto operate.

Syntax

The following is the general SQL syntax for the UPDATE statement to modify data:

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

• We can update one or more fields at the same time.
• We can specify any condition in the WHERE clause.

Examples

Create the COMPANY table (Download COMPANY SQL file), the data content is as follows:

exampledb# select * from COMPANY;
 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
(7 rows)

The following example will update the value of the salary field for id 3 in the COMPANY table:

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

The result is as follows:

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

From the results, the value of the salary field for id 3 in the COMPANY table has been modified.

The following example will update the values of the salary field and the address field at the same time:

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

The result is as follows:

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