How to update table using correlated query in postgresql..?

Question

How to update using correlated query I am using postgresql?

Here is my example

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

Output:

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

select * from t1

Output:

 - 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

Output:

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

but I am expecting answer like this

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

thanks in advance :)

Answer 1

You can do:

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

sqlfiddle demo