postgresql - 如何在 postgresql 中使用相关查询更新表..？

Question

如何使用相关查询更新我正在使用 postgresql？

这是我的例子

CREATE TABLE t("no" integer,"name" character varying)
CREATE TABLE t1(no1 integer,name1 character varying)

INSERT INTO t("no", "name")VALUES (1, 'A');
INSERT INTO t("no", "name")VALUES (2, 'B');
INSERT INTO t("no", "name")VALUES (3, 'C');
INSERT INTO t("no", "name")VALUES (4, 'D');

INSERT INTO t1("no1", "name1")VALUES (1, 'Z');
INSERT INTO t1("no1", "name1")VALUES (2, 'Y');
INSERT INTO t1("no1", "name1")VALUES (3, 'X');
INSERT INTO t1("no1", "name1")VALUES (4, 'W');

select * from t

输出:

 - no    name
 - 1     A
 - 2     B
 - 3     C
 - 4     D

select * from t1

输出:

 - no1   name1
 - 1     Z
 - 2     Y
 - 3     X
 - 4     W

update t  
set name = T1.name 
from (select no1,name1 from t1) T1 
where t.no = T1.no1

select * from t

输出:

 - no       name
 - 1       (1,Z)
 - 2       (2,Y)
 - 3       (3,X)
 - 4       (4,W)

但我期待这样的回答

 - no       name
 - 1         Z
 - 2         Y
 - 3         X
 - 4         4

提前致谢:)

Answer 1

你可以这样做:

UPDATE t
SET "name" = t1.name1
FROM t1
WHERE t.no = T1.no1

sqlfiddle demo