TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

PostgreSQL Update

PostgreSQL Update Statement for beginners and professionals with examples database, table, create, select, insert, update, delete, join, function, index, clause, trigger, view, procedure etc.

<< Back to POSTGRESQL

PostgreSQL Update

In this tutorial, we are going to learn the PostgreSQL UPDATE command for updating the current data in a table.

In PostgreSQL, the UPDATE command is used to change the present records in a table. To update the selected rows, we have to use the WHERE clause; otherwise, all rows would be updated.

Syntax of Update command

The syntax of update command is as follows:

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

We have the following parameters, which are used in the above syntax:

Parameters Description
Update It is a keyword, which is used to update the rows of a table.
Table_name After the UPDATE clause, we will use this parameter to define the table name to update the data.
Column1 = value1,
Column2 = value2,
......
ColumnN = valueN
It is used to describe a column's name in a table whose values need to be modified in the SET clause. We can use the comma (,) to separate every pair of the column and values.
Where We will use the WHERE clause to filter the records and fetch only the essential records.
Condition It is an expression, which is used to return a value of type Boolean. And this expression returns true only for rows.

Examples of PostgreSQL update command

For our better understanding, we will see examples of PostgreSQL Update command.

We will take the department table, which we created in the Insert command section.

Firstly, we will see the structure of the department table with the help of below command:

SELECT * FROM department;

After executing the select command, we will get the below result:

PostgreSQL Update

Example of PostgreSQL UPDATE table

Here, we will modify the NULL values of the last_update column to the Current date with the below command's help:

UPDATE department
SET last_update = DEFAULT
WHERE
last_update IS NULL;

We will get the below outcome, after performing the above command:

PostgreSQL Update

In the above query, we apply the DEFAULT keyword to the last_update column that takes the current date as per the default value. And the WHERE clause updates only those rows where the last_update column value is NULL.

Example of upgrading all rows in a table using the Update command

To update the values in the location column as U.S.A for all rows in the department table, we use the following command:

Note: In the below update command, we ignore the WHERE clause:

UPDATE department
SET location = 'U.S.A';

After executing the above query, it will update the location column in the department table:

PostgreSQL Update

We will use the select command to verify the output of the above command in the department table:

select* from department;

PostgreSQL Update

And within the same table, we can also update data of a column from an additional column.

The below command is used to copies the dept_name column's values to the description column of the department table:

UPDATE department
SET description = dept_name;

Once we perform the above query, it will update the description column in the department table:

PostgreSQL Update

After that, we will use the select command to see the updated column(description) in the department table:

PostgreSQL Update

Example of PostgreSQL update joins command

Here we will take the department_tmp table, which has the same structure as the department table:

The following command is used to update values, which come from the department table for the columns in the department_tmp table:

UPDATE department_tmp
SET location = department.location,
description = department.description,
last_update = department.last_update
FROM
department
WHERE
department_tmp.Dept_id = department.Dept_id;

Once we perform the above query, it will update the department_tmp table:

PostgreSQL Update

To verify the department_tmp table, we will use the Select command:

SELECT * FROM department_tmp; 

And, we will get the below output:

PostgreSQL Update

Note: In the above update command, we used the FROM clause to describe the second table (department), which contains in the update.

Here we used the join condition in the WHERE clause. And sometimes this UPDATE command mentioned as UPDATE INNER JOIN or UPDATE JOIN as two or more tables are involved in the UPDATE command.

Update command through returning condition

By default, the update command can return the number of affected rows, and it also returns the efficient data with the help of the Returning section.

The below command is used to update the row with Dept_id 1 in the department table and return the updated data.

UPDATE department
SET description = 'Names of departments',
location = 'NewYork'
WHERE
dept_id = 1 
RETURNING dept_id,
description,
location;

After executing the above command, we will get the below table structure:

PostgreSQL Update

After that, we will use the select command to check the updated data in the department table:

SELECT
*
FROM
department
WHERE
dept_id = 1;

Once we run the above Select query, we get the below output:

PostgreSQL Update
Next TopicPostgreSQL Delete




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf