How to Update and Join Statements in PostgreSQL

  2. HowTo
  3. PostgreSQL Howtos
  4. How to Update and Join Statements in …
Joy Idialu Feb 02, 2024
Postgres Postgres JOIN
  1. Produce Shipment Example in PostgreSQL
  2. Use the UPDATE and JOIN Statements to Update the Table in PostgreSQL
  3. Use the UPDATE Statement Only to Update the Table in PostgreSQL
How to Update and Join Statements in PostgreSQL

This tutorial will walk you through updating a table using UPDATE and JOIN statements.

Produce Shipment Example in PostgreSQL

Consider a store that sells food produce. The product’s price is updated to reflect the current market price whenever a shipment arrives.

In this example, there are two tables: Produce and Shipment.

Produce table:

|id  | price|
-----|-------
|  1 |     0|
|  2 |     0|
|  3 |     0|
|  4 |     0|
|  5 |     0|
|  6 |     0|
|  7 |     0|
|  8 |     0|
|  9 |     0|
| 10 |     0|

 CREATE TABLE produce
(
    id integer NOT NULL
        GENERATED ALWAYS AS IDENTITY,
    price integer NOT NULL,
    CONSTRAINT pk_produce PRIMARY KEY (id)
)

Here’s the INSERT statement to populate the Produce table with a default cost of 0:

INSERT INTO produce(price)
SELECT 0
FROM generate_series(1,10) i

Shipment table:

|id  | produce_id | produce_price|
-----|------------|---------------
|  1 |          1 |           193|
|  2 |          2 |            41|
|  3 |          3 |           184|
|  4 |          4 |           192|
|  5 |          5 |           174|
|  6 |          6 |           122|
|  7 |          7 |            70|
|  8 |          8 |           130|
|  9 |          9 |           105|
| 10 |         10 |           176|

CREATE TABLE shipment
(
    id integer NOT NULL
        GENERATED ALWAYS AS IDENTITY,
    produce_id integer NOT NULL,
    produce_price integer NOT NULL,
    CONSTRAINT pk_shipment PRIMARY KEY (id),
    CONSTRAINT fk_shipment_produce FOREIGN KEY (produce_id) REFERENCES produce (id)
)

Here’s the INSERT statement to populate the Shipment table with test data:

INSERT INTO shipment (produce_id, produce_price)
SELECT i, floor(random()*(200-20+1))+20
FROM generate_series(1,10) i

Use the UPDATE and JOIN Statements to Update the Table in PostgreSQL

The price field in the Produce table is updated from the shipment’s produce_price field as shown here:

UPDATE produce
SET price = s.produce_price
FROM shipment AS s
LEFT JOIN produce AS p ON s.produce_id = p.id
WHERE produce.id = p.id

Use the UPDATE Statement Only to Update the Table in PostgreSQL

An optimized and cleaner approach can be implemented using only the UPDATE statement as shown here:

UPDATE produce AS p
SET price = s.produce_price
FROM shipment AS s
WHERE p.id = s.produce_id

Output:

|id  | price|
-----|-------
|  1 |   193|
|  2 |    41|
|  3 |   184|
|  4 |   192|
|  5 |   174|
|  6 |   122|
|  7 |    70|
|  8 |   130|
|  9 |   105|
| 10 |   176|