问题:有两张表 users, addrs,需要从users表中删除一些行,条件为在“addrs"表中没有对应id的行。
事实上很简单,只是个left join查询,SELECT a.*,'|' s,b.* FROM users a LEFT JOIN addrs b ON a.id=b.id;
,预览如下:
然而,postgres本身不支持 DELETE ... LEFT JOIN ... 这样的语句,这点不像MySQL那样方便。
有一个曲线救国方法:
把上面LEFT JOIN的结果表视为一张中间表,把原users表、按主键 inner join 到中中间表,然后从users表中按删除指定行。语句如下
DELETE FROM users USING users a LEFT JOIN addrs b ON a.id=b.id WHERE users.id=a.id AND b.name IS NULL;
问题得解。
当然也可以使用子查询,从中间表中取出id主键,以子查询形式删除。不推荐。
前文用表及数据
CREATE TABLE users ( id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY, name character varying(255) ); ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id); CREATE TABLE addrs ( id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY, addr character varying(255) ); ALTER TABLE addrs ADD CONSTRAINT addrs_pkey PRIMARY KEY (id); INSERT INTO "users" VALUES (1, 'Tom'); INSERT INTO "users" VALUES (2, 'Jarry'); INSERT INTO "users" VALUES (3, 'Alias'); INSERT INTO "users" VALUES (4, 'Bob'); INSERT INTO "users" VALUES (5, 'somebody'); INSERT INTO "addrs" VALUES (1, 'Luoyang'); INSERT INTO "addrs" VALUES (2, 'Shanghai'); INSERT INTO "addrs" VALUES (3, 'Hongkong');
EOF.