迹忆客 专注技术分享

本文将引导你使用 UPDATE 和 JOIN 语句更新表。

在 PostgreSQL 中生成发货示例

考虑一家销售食品的商店。每当货物到达时，产品的价格就会更新以反映当前的市场价格。

在此示例中，有两个表：Produce 和 Shipment。

生产表：

|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)
)

这是用默认成本 0 填充 Produce 表的 INSERT 语句：

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

出货表：

|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)
)

这是用测试数据填充 Shipment 表的 INSERT 语句：

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

使用 UPDATE 和 JOIN 语句更新 PostgreSQL 中的表

Produce 表中的 price 字段从货件的 produce_price 字段更新，如下所示：

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

仅使用 UPDATE 语句更新 PostgreSQL 中的表

可以仅使用 UPDATE 语句来实现优化且更简洁的方法，如下所示：

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

输出：

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