PostgreSQL Update

In PostgreSQL, Update statement is used to modify existing rows in table. UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause. We must have the UPDATE privilege on the table, or at least on the column(s) that are listed to be updated.

PostgreSQL Update Syntax:

UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

PostgreSQL Update Examples:

Lets create a table for below examples:

 CREATE TABLE students(SNO int PRIMARY KEY, SNAME varchar(50),DOB date, class int, gender varchar(6));

PostgreSQL Update

1. Change the 2001-12-23 to 2009-02-23 in the column dob of the table students.

update students set dob='2009-02-23' where dob='2001-12-23';

After this run select * from students;

 sno | sname  |     dob     | class | gender
-----+--------+-------------+-------+--------
 102 | Varshi | 2011-12-23  |     5 | female
 103 | Nancy  | 2008-02-13  |     5 | female
 104 | James  | 2007-02-13  |     8 | male
 105 | David  | 20012-05-08 |     3 | male
 101 | Vijay  | 2009-02-23  |     4 | male

2. Change class to 5 for the student number(SNO)=104.

update students set class=5 where SNO=104;

3. Change Name to Cameron for the student number(SNO)=102.

update students set sname='Cameron' where sno=104;

4. In employee table, increase sal by 500 where job=clerk in PostgreSQL.

update emp set sal=sal+500 where job='CLERK';

Before update command

After update command.

5. Change the 20012-05-08 to 2012-05-08 in the column dob of the table students.

update students set dob='2012-05-08' where sno=105;

No related posts.