仅当主键存在时更新外键

Question

我有以下一段代码。我想更新一个外键如果对应的主键存在。

请参考查询No: 2和查询No: 3

DROP SCHEMA IF EXISTS s CASCADE;  
CREATE SCHEMA s;
CREATE TABLE s.t1 (
    "id1" BigSerial,
    "id2" BigInt,
    CONSTRAINT "pk1" PRIMARY KEY (id1)
)
WITH(OIDS=FALSE);    
CREATE TABLE s.t2 (
    "id3" BigSerial,
    "id4" Varchar UNIQUE NOT NULL,
    CONSTRAINT "pk2" PRIMARY KEY (id3)
)
WITH(OIDS=FALSE);
ALTER TABLE s.t1 ADD CONSTRAINT "fk" FOREIGN KEY (id2) REFERENCES s.t2 (id3) ON DELETE NO ACTION ON UPDATE NO ACTION;       
INSERT INTO s.t2 (id3, id4) VALUES (1, 'hello');
INSERT INTO s.t2 (id3, id4) VALUES (21, 'hello2');
INSERT INTO s.t2 (id3, id4) VALUES (31, 'hello3');
INSERT INTO s.t1 (id1, id2) VALUES (2, 21);
INSERT INTO s.t1 (id1) VALUES (3);
SELECT id1, id2 FROM s.t1;
UPDATE s.t1
SET id2 = (SELECT id3 FROM s.t2 WHERE s.t2.id4 = 'hello') WHERE s.t1.id1 = 2; 
SELECT id1, id2 FROM s.t1;

查询2

UPDATE s.t1
SET id2 = (SELECT id3 FROM s.t2 WHERE s.t2.id4 = 'xyz') WHERE s.t1.id1 = 2
AND EXISTS (SELECT 1 FROM s.t2 WHERE s.t2.id4 = 'xyz');
SELECT id1, id2 FROM s.t1;

查询3

UPDATE s.t1
SET id2 = (SELECT id3 FROM s.t2 WHERE s.t2.id4 = 'hello3') WHERE s.t1.id1 = 2
AND EXISTS (SELECT 1 FROM s.t2 WHERE s.t2.id4 = 'hello3');
SELECT id1, id2 FROM s.t1;
DROP SCHEMA s CASCADE;  

是否有可能通过连接以有效的方式执行上述查询?我不希望两次调用本质上相同的子查询。我目前正在这样做。一次用于EXISTS语句，一次用于主查询。

Answer 1

您可以使用CTE只计算一次子查询，然后多次使用它:

WITH find_rec AS
  (SELECT id3 FROM s.t2 WHERE id4 = 'hello3')
UPDATE s.t1
SET id2 = find_rec.id3
WHERE id1 = 2
  AND EXISTS (SELECT 1 FROM find_rec);

一般来说，您应该永远不要手动为bigserial列分配值。下次从id1或id3列后面的序列创建记录时，您将得到一个主键冲突。bigserial类型的全部目的是让PostgreSQL管理主键值。