PostgreSQL Update

The modification of data that is already in the database is referred to as updating. You can update individual rows, all the rows in a table, or a subset of all rows. Each column can be updated separately; the other columns are not affected.

To perform an update, you need three pieces of information:

1. The name of the table and column to update,
2. The new value of the column,
3. Which row(s) to update.

EXAMPLE 1:
--this command updates all products that have a price of 10 to have a price of 22:
--before update products table

postgres=# select * from products;                                  
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
          2 | bargar |      
          3 | juice  |      
          4 | cake   |      
            |        |      
          1 | laptop |    20
          2 | pen    |    10
(9 rows)

postgres=# UPDATE products SET price = 22 WHERE price = 10;
UPDATE 1

--After update products table

postgres=# select * from products;                         
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
          2 | bargar |      
          3 | juice  |      
          4 | cake   |      
            |        |      
          1 | laptop |    20
          2 | pen    |    22
(9 rows)

Example 2:Updating all column in a table(full table updated)
--if you want to raise the price of all products by 10% you could use:

postgres=# UPDATE products SET price = price * 1.10;
UPDATE 9

--After update products table

postgres=# select * from products;                  
 product_no |  name  |  price  
------------+--------+---------
          1 | Cheese |  110.00
          1 | Cheese | 10.9890
          1 | Cheese | 10.9890
          2 | bargar |        
          3 | juice  |        
          4 | cake   |        
            |        |        
          1 | laptop |   22.00
          2 | pen    |   24.20
(9 rows)

Example 3:
You can update more than one column in an UPDATE command by listing more than one assignment in the SET clause. For example:

postgres=# UPDATE products SET name ='cocacola', product_no= 5  WHERE price=10.9890; 
UPDATE 2

postgres=# select * from products;                                                   
product_no |   name   |  price  
------------+----------+---------
          1 | Cheese   |  110.00
          2 | bargar   |        
          3 | juice    |        
          4 | cake     |        
            |          |        
          1 | laptop   |   22.00
          2 | pen      |   24.20
          5 | cocacola | 10.9890
          5 | cocacola | 10.9890
(9 rows)