Introduction to PostgreSQL Update
PostgreSQL update statement is used to update the existing data from the table, we use the update statement to update the existing data from the table. Update query is used to modify the existing data from the table, we have used where clause with update statement to modify or update the selected rows if we have not used update statement all rows will be updated from the table. The update statement is very useful and important in PostgreSQL to modify the existing rows from the table. If we have to update the existing row after table creation then we have to use an update statement.
Syntax
Below is the syntax of the update query in PostgreSQL.
1. Update statement without using where clause
Update name_of_table (Table name from which we have modifying row.) SET
name_of_column1 = value1 (Value which we have setting to the column.),
name_of_column2 = value2 (Value which we have setting to the column.), …,
name_of_columnN = valueN (Value which we have setting to the column.);
2. Update statement with where clause
Update name_of_table (Table name from which we have modifying row.) SET
name_of_column1 = value1 (Value which we have setting to the column.),
name_of_column2 = value2 (Value which we have setting to the column.), …,
name_of_columnN = valueN (Value which we have setting to the column.) where [condition];
Below is the parameter description syntax of the update statement in PostgreSQL.
- Update –This statement is used to modify the existing rows from the table.
- Table name –This is defined as the table name from which we have updating rows. We have updated selected or all rows from the table.
- Set –This keyword is used to set the value of table column rows which was we have set.
- Column name –We have used column name with update statement in PostgreSQL. We have used a column name from which column we have modified the value from the table. We can change multiple columns or single column values using the update command.
- Value –This is defined as the value which we have a setting to column rows. We have set different values to a different column in PostgreSQL.
- Where condition – We have used the where clause to update specific rows in PostgreSQL. If we have not used the where condition with the update statement then it will update all rows from the table.
How to Update statement work in PostgreSQL?
Below is the working of the update statement in PostgreSQL. To execute the update statement in PostgreSQL we need to have update privileges on the table or we need to have superuser privileges to execute the statement.
Below is the example of an update statement requiring update privileges on the table or superuser privileges to execute the update statement in PostgreSQL.
psql -U db_test -d testing
Update student set stud_name_new = 'ABC' where stud_name_new = 'PQR';
psql -U Postgres
\c testing;
Update student set stud_name_new = 'ABC' where stud_name_new = 'PQR';
- In the above first example, we have used user as db_test, this user doesn’t have privileges of update student table or superuser so, it will issue an error while executing the update statement.
- In the second example, we have updated the table rows using the username as Postgres, after using this user we have to update the column name of stud_name_new from PQR to ABC from the table student.
- We have used an update statement to modify the existing rows by using the where clause in PostgreSQL.
- Once the data is inserted into the table it will modify by using the update command. We can update the column values by using the identifier, constants, expression, and by using other data types in PostgreSQL.
- This condition will be applied to the specified column or all columns with the condition which was we have used in the update query.
- If we have to update all rows from the table, this is very easy without using where clause with an update statement in PostgreSQL.
- We have updated a single column as well as multiple columns by using an update statement.
- We have also used from clause to update the table by using other data sets or tables. We are using select operations on other data sets to update the rows from the table.
- Also, we have used a join condition to update the rows from the table. We have used two tables with join conditions to update rows in PostgreSQL.
Examples
Below is the example of an update statement is as follows. We are using stud1 and stud2 tables to describe the example of the update statement.
Below is the table and data description of the stud1 table.
\d+ stud1;
select * from stud1;
Below is the table and data description of the stud2 table.
\d+ stud2;
select * from stud2;
Example #1 – Update statement without using where clause
The below example shows that update statement without using where clause. We have updated the name from ABC to PQR from the stud2 table.
Update stud2 set name = 'PQR';
select * from stud2;
Example #2 – Update statement using where clause
The below example shows that update statement using where clause. We have updated the name ABC which has id is 11.
Update stud2 set name = 'ABC' where id =11;
select * from stud2;
Example #3 – Update statement using multiple columns
The below example shows that update statement using multiple columns.
Update stud1 set id = 12, name = 'XYZ', address = 'Pune' where id = 11;
select * from stud1;
Recommended Articles
This is a guide to PostgreSQL Update. Here we discuss How to Update statement work in PostgreSQL along with the Syntax and Examples. You may also have a look at the following articles to learn more –
