Update join
Update join用于基于另一张表更新表数据，语法如下：
UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;
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);
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);
update product p
set net_price = price - price * discount
from product_segment s
where p.segment_id = s.id
文章来源：https://www.toymoban.com/news/detail-575335.html
Delete using
DELETE FROM table_name1
USING table_expression
WHERE condition
RETURNING returning_columns;
using 关键字后面指定表，可以是多个
然后where子句中可以使用using 后面指定表的字段文章来源地址https://www.toymoban.com/news/detail-575335.html
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');
delete from contacts
using blacklist
where contacts.phone=blacklist.phone
到了这里，关于8.postgresql--Update join 和 Delete using的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
