关于PostgreSQL update 关于子查询的问题

[kanyj]

在PostgreSQL
update table1 t1 set t1.name=(select t2.name from table2 t2 where t1.id=t2.id)
where exists (select 1 from table2 t2 where t1.id=t2.id)

中这样的语句应该怎么写


貌似PostgreSQL 中upate，delete 都不支持子查询

[kanyj]

怎么没人回答啊，自己顶

[ACMAIN_CHM]

UPDATE是支持子查询的。

比如下面的例子。
UPDATE employees SET sales_count = sales_count + 1 WHERE id =
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

你的错误信息是什么？


问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）


当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html

8、如何给分和结贴？
http://community.csdn.net/Help/HelpCenter.htm#结帖

[trainee]

8楼的语句没错。但不知会有这错误提示

[wwwwb]

什么版本？
update table1 t1 set t1.name=t2.name
from table1 inner join table2 t2 on t1.id=t2.id

[kanyj]

原SQL 是这样的
Update DW_CUUSER_MI_M3390_201010 t1 set
belongs_area_code = (select t2.belongs_area_code
from dw_pz_cuuser_mi_200712 t2
where t1.user_id = t2.user_id
and t1.belongs_area_code<>t2.belongs_area_code
and t1.create_date < to_date('20080101', 'yyyymmdd')
and t1.svcid = 'G' and t1.area_id='390'
and t1.svcsubid = 2)
where exists (select 1
from dw_pz_cuuser_mi_200712 t2
where t1.user_id = t2.user_id
and t1.belongs_area_code<>t2.belongs_area_code
and t1.create_date < to_date('20080101', 'yyyymmdd')
and t1.svcid = 'G' and t1.area_id='390'
and t1.svcsubid = 2)
错误
ERROR: Greenplum Database does not yet support that query.
DETAIL: The query contains a correlated subquery.

********** 错误 **********

ERROR: Greenplum Database does not yet support that query.
SQL 状态: 0AM00
详细:The query contains a correlated subquery.

希望大虾们帮忙看下是什么原因

[wwwwb]

什么版本？

[kanyj]

8.4版本的

[wwwwb]

示例：
UPDATE "DD2" SET "XM"="DD1"."XM" FROM "DD1" WHERE "DD1"."ID"="DD2"."ID"

[kanyj]

Update DW_CUUSER_MI_M3390_201010 t1 set belongs_area_code=t2.belongs_area_code
from dw_pz_cuuser_mi_200712 t2 where t1.user_id = t2.user_id
and t1.belongs_area_code<>t2.belongs_area_code
and t1.create_date < to_date('20080101', 'yyyymmdd')
and t1.svcid = 'G' and t1.area_id='390'
and t1.svcsubid = 2

报错：
ERROR: Cannot parallelize that UPDATE yet
DETAIL: Passage of data from one segment to another is not yet supported during UPDATE operations.
HINT: The WHERE condition must specify equality between corresponding DISTRIBUTED BY columns of the target table and all joined tables.

********** 错误 **********

ERROR: Cannot parallelize that UPDATE yet
SQL 状态: 0AM01
详细:Passage of data from one segment to another is not yet supported during UPDATE operations.
指导建议:The WHERE condition must specify equality between corresponding DISTRIBUTED BY columns of the target table and all joined tables.

我改为 inner join 的也不行

[wwwwb]

大写的表名要加双引号

[kanyj]

Update "DW_CUUSER_MI_M3390_201010" set "belongs_area_code"="dw_pz_cuuser_mi_200712"."belongs_area_code"
from "dw_pz_cuuser_mi_200712" where "DW_CUUSER_MI_M3390_201010"."user_id"= "dw_pz_cuuser_mi_200712"."user_id"
and "DW_CUUSER_MI_M3390_201010"."belongs_area_code"<>"dw_pz_cuuser_mi_200712"."belongs_area_code"
and "DW_CUUSER_MI_M3390_201010"."create_date" < to_date('20080101', 'yyyymmdd')
and "DW_CUUSER_MI_M3390_201010"."svcid" = 'G' and "DW_CUUSER_MI_M3390_201010"."area_id"='390'
and "DW_CUUSER_MI_M3390_201010"."svcsubid" = 2

这样写还有错的，请楼上帮写下应该怎么写，非常感谢