Introduction to the PostgreSQL UPDATE join syntax

Sometimes, you need to update data in a table based on values in another table. In this case, you can use the PostgreSQLUPDATE join.

Here’s the basic syntax of theUPDATE join statement:

UPDATE table1SET table1.c1= new_valueFROM table2WHERE table1.c2= table2.c2;

To join a table (table1) with another table (table2) in theUPDATE statement, you specify the joined table (table2) in theFROM clause and provide the join condition in theWHERE clause. TheFROM clause must appear immediately after theSET clause.

For each row of the tabletable1, theUPDATE statement examines every row of the tabletable2.

If the values in thec2 column of tabletable1 equals the values in thec2 column of tabletable2, theUPDATE statement updates the value in thec1 column of the tabletable1 the new value (new_value).

PostgreSQL UPDATE JOIN example

Let’s take a look at an example to understand how the PostgreSQLUPDATE join works. We will use the following database tables for the demonstration:

First,create a new table calledproduct_segment that stores the product segments such as grand luxury, luxury, and mass.

Theproduct_segment table has thediscount column that stores the discount percentage based on a specific segment. For example, products with the grand luxury segment have5% discount while luxury and mass products have6% and10% discounts respectively.

CREATE TABLE product_segment (    idSERIAL PRIMARY KEY,    segmentVARCHAR NOT NULL,    discountNUMERIC (4,2));INSERT INTO    product_segment (segment, discount)VALUES    ('Grand Luxury',0.05),    ('Luxury',0.06),    ('Mass',0.1);

Second, create another table namedproduct that stores the product data. Theproduct table has theforeign key columnsegment_id that links to theid of thesegment table.

CREATE TABLE product(    idSERIAL PRIMARY KEY,    name VARCHAR NOT NULL,    priceNUMERIC(10,2),    net_priceNUMERIC(10,2),    segment_idINT NOT NULL,    FOREIGN KEY(segment_id)REFERENCES product_segment(id));INSERT INTO    product (name, price, segment_id)VALUES    ('diam',804.89,1),    ('vestibulum aliquet',228.55,3),    ('lacinia erat',366.45,2),    ('scelerisque quam turpis',145.33,3),    ('justo lacinia',551.77,2),    ('ultrices mattis odio',261.58,3),    ('hendrerit',519.62,2),    ('in hac habitasse',843.31,1),    ('orci eget orci',254.18,3),    ('pellentesque',427.78,2),    ('sit amet nunc',936.29,1),    ('sed vestibulum',910.34,1),    ('turpis eget',208.33,3),    ('cursus vestibulum',985.45,1),    ('orci nullam',841.26,1),    ('est quam pharetra',896.38,1),    ('posuere',575.74,2),    ('ligula',530.64,2),    ('convallis',892.43,1),    ('nulla elit ac',161.71,3);

Third, suppose you have to calculate the net price of every product based on the discount of the product segment. To do this, you can apply theUPDATE join statement as follows:

UPDATE productSET net_price= price- price* discountFROM product_segmentWHERE product.segment_id= product_segment.id;

You can utilize the table aliases to make the query shorter like this:

UPDATE    product pSET    net_price= price- price* discountFROM    product_segment sWHERE    p.segment_id= s.id;

This statement joins theproduct table to theproduct_segment table. If there is a match in both tables, it gets a discount from theproduct_segment table, calculates the net price based on the following formula, and updates thenet_price column.

net_price = price - price * discount;

The followingSELECT statement retrieves the data of theproduct table to verify the update:

SELECT * FROM product;

The output indicates that thenet_price column has been updated with the correct values.

Summary

• Use the PostgreSQLUPDATE join statement to update data in a table based on values in another table.

Last updated onFebruary 1, 2024

On this page

• Introduction to the PostgreSQL UPDATE join syntax
• PostgreSQL UPDATE JOIN example
• Summary