提要:本教程将展示如何通过 PostgreSQL 的 UPDATE 关联语法将另一张表里的数据更新到当前表。

PostgreSQL 的 UPDATE 关联语法简介

有时候,需要从另一张表里拿到数据,然后将这些数据更新到当前表的对应记录里。此时,可以使用以下的 UPDATE 关联语法:

UPDATE A
SET A.c1 = expresion
FROM B
WHERE A.c2 = B.c2;

要使用 UPDATE 关联更新,你需要:

  • 通过 FROM 指定关联的表
  • 通过 WHERE 指定关联条件
  • FROMWHERE 都在 SET 之后

PostgreSQL 的 UPDATE 关联示例

我们将使用下面的数据库做演示:

模型

首先,创建 product_segment 表,存储产品类目。product_segment 表有一个 discount 字段,用于存储基于特定产品的折扣百分比:

CREATE TABLE product_segment (
    ID SERIAL PRIMARY KEY,
    segment VARCHAR NOT NULL,
    discount NUMERIC (4, 2)
);


INSERT INTO product_segment (segment, discount)
VALUES
    ('Grand Luxury', 0.05),
    ('Luxury', 0.06),
    ('Mass', 0.1);

然后,创建用于存储产品数据的 product 表。该表有一个 segment_id 外键字段,用于和 product_segment 表进行关联。

CREATE TABLE product(
    id serial primary key,
    name varchar not null,
    price numeric(10,2),
    net_price numeric(10,2),
    segment_id int 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);

product 表的数据如下:

product

第三,假设你必须根据产品类目的折扣来计算每个产品的净价格,可以使用下面的语句:

UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;

这条语句将 productproduct_segment 进行关联。如果两张表都有记录,它将从 product_segment 表里获取 discount 字段的值,然后计算净价格(net_price)。

来看看 product 现在的数据:

SELECT
    *
FROM
    product;

product

上一篇:PostgreSQL的UPDATE
下一篇:PostgreSQL的UPSERT