Postgres Update Query

The Postgres update query is used to modify the table data. Update query changes the values of all rows satisfying the specified criteria. You need to specify column names that you want to modify. Column names that are omitted will carry their old values.

Let’s see update query syntax:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition
RETURNING * | output_expression AS output_name;

In the above syntax,

table_name is name of the table you want to update.

columns names which we want to update are specified after SET keyword.

condition is the criteria which you specify to update specific rows in the table as per requirement.

RETURNING is the optional clause that returns the updated row.

The WHERE clause is optional. If you omit the WHERE clause, the UPDATE statement will update all rows in the table.

When update statement returns successfully it returns:

UPDATE count

The count is the number of rows updated.

Let’s see its example:

CREATE TABLE coding(
	language_id serial primary key,
	language_name VARCHAR(255) NOT NULL

);

INSERT INTO 
	coding(language_name)
VALUES

	('JAVA');

UPDATE coding
SET language_name = 'PHP' 
WHERE language_id = 1;

The above update statement changes language_name to PHP.

UPDATE coding
SET language_name = 'PHP' 
WHERE language_id = 1;
RETURNING *;

Above statement returns updated rows from the coding table. You can also use join clause in Postgres update query.

We can modify the column(s) to the DEFAULT value(s) of the respected columns, by specifying the value(s) as DEFAULT for the column(s) in the SET clause of the UPDATE query.