http://forums.enterprisedb.com/posts/list/7.page JForum - http://www.jforum.net create table t2 (c1 int, c2 int);

update t1
set c2 = t2.c2
from t2
where t2.c1 = t1.c1

Result:

SQLException: ERROR: Encountered "from" at line 1, column 26.
Was expecting one of:
<EOF>
"WHERE" ...
"!" ...
";" ...
"," ...
"::" ...
"+" ...
"-" ...
"||" ...
"*" ...
"/" ...
"MOD" ...
"DIV" ...
"%" ...
"&" ...
"|" ...
"#" ...
"<<" ...
">>" ...]]> http://forums.enterprisedb.com/posts/preList/1552/5419.page http://forums.enterprisedb.com/posts/preList/1552/5419.page GMT
You need to use syntax like this:

update t1
set c2 =
(select t2.c2
from t2
where t2.c1 = t1.c1);

Regards,

Mason Sharp
]]> http://forums.enterprisedb.com/posts/preList/1552/5422.page http://forums.enterprisedb.com/posts/preList/1552/5422.page GMT http://forums.enterprisedb.com/posts/preList/1552/5424.page http://forums.enterprisedb.com/posts/preList/1552/5424.page GMT
I have added multi-column correlated update as a new feature request in our tracking system.

Regards,

Mason]]> http://forums.enterprisedb.com/posts/preList/1552/5427.page http://forums.enterprisedb.com/posts/preList/1552/5427.page GMT Your suggested workaround does work, but fails when a where clause is needed to reduce the rows to be updated. For example:


update t1
set c2 = (select t2.c2 from t2 where t2.c1 = t1.c1)
where exists (select * from t2 where t2.c1 = t1.c1);

Result:

SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT ( SELECT "t2"."c2" From "t2" WHERE ("t2"."c1" = "TMPTT433_1"."c1")) as "EXPRESSION1", "c1" as "c1_old", "c2" as "c2_old" FROM "TMPTT433_1" ) eQS Node 13 has aborted execution, cause is: java.sql.SQLException : ERROR: column "c2" does not exist

]]> http://forums.enterprisedb.com/posts/preList/1552/5438.page http://forums.enterprisedb.com/posts/preList/1552/5438.page GMT
That does indeed look like a problem.

We will investigate.

Regards,

Mason]]> http://forums.enterprisedb.com/posts/preList/1552/5445.page http://forums.enterprisedb.com/posts/preList/1552/5445.page GMT xtest-> (select b.SONGNAME from D_QUANGQU AS B where b.CONTENTID=RING_ID)
xtest-> ;
ERROR: Node 14 has aborted execution, cause is: java.sql.SQLException : [color=red]ERROR: more than one row returned by a subquery used as an expression[/color]xtest=> UPDATE T_JH_M_QUANGQU_DATE_201003 SET RING_NAME=
(select min(b.SONGNAME) from D_QUANGQU AS B where b.CONTENTID=RING_ID);
[color=green]Cancel request sent
Cancel request sent
Cancel request sent
Cancel request sent
Cancel request sent[/color]


i have two question:
1.when update statement using two tables , error "ERROR: more than one row returned by a subquery used as an expression" how can i do?
2. i can not ctrl_c to interrupt it ??

]]> http://forums.enterprisedb.com/posts/preList/1552/9464.page http://forums.enterprisedb.com/posts/preList/1552/9464.page GMT xtest-> (select b.SONGNAME from D_QUANGQU AS B where b.CONTENTID=RING_ID)
xtest-> ;
ERROR: Node 14 has aborted execution, cause is: java.sql.SQLException : [color=red]ERROR: more than one row returned by a subquery used as an expression[/color]xtest=> UPDATE T_JH_M_QUANGQU_DATE_201003 SET RING_NAME=
(select min(b.SONGNAME) from D_QUANGQU AS B where b.CONTENTID=RING_ID);
[color=green]Cancel request sent
Cancel request sent
Cancel request sent
Cancel request sent
Cancel request sent[/color]


[color=blue]i have two question:
1.when update statement using two tables , error "ERROR: more than one row returned by a subquery used as an expression" how can i do?
2. i can not ctrl_c to interrupt it ??[/color]
]]> http://forums.enterprisedb.com/posts/preList/1552/9465.page http://forums.enterprisedb.com/posts/preList/1552/9465.page GMT
We will look into this when we have a chance.

Yes, you should be able to cancel a query. If there still is a problem, you could cancel on the individual backends.

Mason]]> http://forums.enterprisedb.com/posts/preList/1552/9485.page http://forums.enterprisedb.com/posts/preList/1552/9485.page GMT