请教一条update from where的标准SQL语句
(注:公司倾向于使用sybase,但还没有作最后的决定,所以目前只能先用标准的SQL语句实现)
现有表T_CVG, 主键为(pol_id, cvg_id):
pol_id | cvg_id | plan_id
--------------------------------
P0001 | 01 | PA01
P0001 | 02 | PA02
P0001 | 03 | PA03
P0002 | 01 | PB01
P0002 | 02 | PB02
现在需要把T_CVG中相同pol_id的plan_id放到另一张表T_TMP(主键为pol_id)中的同一行,即T_TMP最后要变成:
pol_id | pol_info
------------------------------------
P0001 | ,PA01, PA02, PA03
P0002 | ,PB01, PB02
(注:假设字段pol_info所允许的最大长度能满足需求)
目前我的思路是分两步:
(1) 先给T_TMP插入pol_id,pol_info默认留为空字符'':
insert into T_TMP(pol_id) select distinct pol_id from T_CVG
(2) 从T_CVG中按pol_id把plan_id的信息update到T_TMP的pol_info字段中:
update T_TMP set
pol_info = pol_info + ',' + a.plan_id
from T_CVG a, T_TMP b
where a.pol_id=b.pol_id
但执行以上语句后,发现T_TMP的pol_info只包含pol_id最后一条plan_id的信息,靠前的plan_id没有添加进去:
pol_id | pol_info
------------------------------------
P0001 | ,PA03
P0002 | ,PB02
请问各位高手,我的update from where语句哪里出错了,该如何写呢?谢谢!