PostgreSQL Update join 和 Delete join 教程

本文学习如何使用 Update join 和 Delete join 语法，实现基于另一张表更新和删除表数据。

Update join

语法介绍

Update join用于基于另一张表更新表数据，语法如下：

UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;

from 关键字后面指定另一张表，where指定关联条件。对于t1表中每一行，update语句检查表t2，如果t1.c2 与 t2.c2 相等，则更新t1.c1为新的值。

示例

下面通过示例展示如何实现：

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 表存储产品类型，其中包括discount 字段存储每种类型对应的折扣，下面创建产品表：

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

产品表的外键 segment_id 链接 类别表id。

下面我们根据类别表的折扣数据计算每个产品的实际价格，我们使用 update join 实现：

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

如果产品表记录在类别表中找到对应记录，则根据对应折扣更新实际价格字段。下面我们讲解类似功能delete join。

Delete join

PostgreSQL不支持 Delete join语句，但支持delete中using 子句提供类似功能：

语法介绍

DELETE FROM table_name1
USING table_expression
WHERE condition
RETURNING returning_columns;

• using 关键字后面指定表，可以是多个
• 然后where子句中可以使用using 后面指定表的字段

举例下面delete语句，使用using指定紧删除t1 中在 t2中的数据:

DELETE FROM t1
USING t2
WHERE t1.id = t2.id

下面通过实际示例进行说明.

示例

首先创建两个表 ： contacts 和 blacklist:

DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts(
   contact_id serial PRIMARY KEY,
   first_name varchar(50) NOT NULL,
   last_name varchar(50) NOT NULL,
   phone varchar(15) NOT NULL
);


DROP TABLE IF EXISTS blacklist;
CREATE TABLE blacklist(
    phone varchar(15) PRIMARY KEY
);


INSERT INTO contacts(first_name, last_name, phone)
VALUES ('John','Doe','(408)-523-9874'),
       ('Jane','Doe','(408)-511-9876'),
       ('Lily','Bush','(408)-124-9221');


INSERT INTO blacklist(phone)
VALUES ('(408)-523-9874'),
       ('(408)-511-9876');

下面我们要删除在 blacklist 中的 联系人：

DELETE FROM contacts 
USING blacklist
WHERE contacts.phone = blacklist.phone;

我们也可以通过子查询实现同样功能：

DELETE FROM contacts
WHERE phone IN (SELECT phone FROM blacklist);