JIYIK CN >

This article guides you through using the UPDATEand JOINstatements to update a table.

Generate a shipment example in PostgreSQL

Consider a store that sells food. Every time a shipment arrives, the price of the product is updated to reflect the current market price.

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

Production 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 is INSERTthe statement that populates the Produce table with a default cost of 0:

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

Shipping List:

|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 is INSERTthe statement that populates 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

Update tables in PostgreSQL using UPDATEthe and statementsJOIN

The price field in the Produce table produce_priceis updated from the shipment's field as follows:

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

UPDATEUpdate a table in PostgreSQL using only the

UPDATEAn optimized and more concise approach can be achieved using only the statement, as follows:

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|