有一张表A如下
A.USER_ID A.DEVELOPER A.DEVELOP_ORGAN
51904806 有一张表B如下
B.USER_ID B.DEVELOP_ORGAN B.DEVELOPER
51904804 SC35658
51904804 NA38430492
51904805 SC35658
51904805 NA38430492
51904806 SC35658
51904806 NA38430492 现在想将满足表B中B.USER_ID='51904806'的两条记录B.DEVELOP_ORGAN='NA38430492' 和 B.DEVELOPER='SC35658'
插入到表A中对应的A.DEVELOPER A.DEVELOP_ORGAN 中。请高手帮忙下如何写语句,谢谢。
A.USER_ID A.DEVELOPER A.DEVELOP_ORGAN
51904806 有一张表B如下
B.USER_ID B.DEVELOP_ORGAN B.DEVELOPER
51904804 SC35658
51904804 NA38430492
51904805 SC35658
51904805 NA38430492
51904806 SC35658
51904806 NA38430492 现在想将满足表B中B.USER_ID='51904806'的两条记录B.DEVELOP_ORGAN='NA38430492' 和 B.DEVELOPER='SC35658'
插入到表A中对应的A.DEVELOPER A.DEVELOP_ORGAN 中。请高手帮忙下如何写语句,谢谢。
update a
set (DEVELOPER,DEVELOP_ORGAN)=(select DEVELOPER,DEVELOP_ORGAN
from b
where a.USER_ID=b.user_id);
是插入到A表还是只更新A表的那两个字段
不好意思,各位大吓,是小弟描述时没有写清楚有一张表A如下
A.USER_ID A.DEVELOPER A.DEVELOP_ORGAN
51904806 空 空
51904803 空 空有一张表B如下
B.USER_ID B.DEVELOP_ORGAN B.DEVELOPER
51904804 空 SC35658
51904804 NA38430492 空
51904805 空 SC35658
51904805 NA38430492 空
51904806 空 SC35658
51904806 NA38430492 空
51904803 NA38430492 SC35658现在想将表B中B.USER_ID='51904806'的两条记录B.DEVELOP_ORGAN='NA38430492' 、 B.DEVELOPER='SC35658' 和
表B中 B.USER_ID='51904803' 的两条记录B.DEVELOP_ORGAN='NA38430492' 、 B.DEVELOPER='SC35658'
插入到表A中A.USER_ID='51904806' A.USER_ID='51904803'对应的A.DEVELOPER A.DEVELOP_ORGAN 中。请高手帮忙下如何写语句,谢谢。
update a
set a.DEVELOP_ORGAN=(select c.DEVELOP_ORGAN
from b c
where a.USER_ID=c.user_id and c.DEVELOP_ORGAN is not null),a.DEVELOPER=(select d.DEVELOPER
from b d
where a.USER_ID=d.user_id and d.developer is not null)
set (a.col1 , a.col2) =
(
select t1.col1, t2.col2
from b t1, b t2
where t1.id = a.id and t1.col1 is not null
and t2.id = a.id and t2.col2 is not null
)
如果猜测不错的话,楼上的语句都会返回:单行子查询返回多于一个行,因为update语句中需要用等号。现在工作忙,中午再来写一个,请楼主过目。
set (a.developer,a.develop_organ)=
(select b.developer,c.develop_organ
from developer_pan02_tbl b,developer_pan02_tbl c
where b.user_id=c.user_id
and b.developer is not null
and c.develop_organ is not null
and a.user_id=b.user_id)经本人调试,可以运行的说!!
USER_ID DEVELOPER DEVELOPER_ORGAN
----------------------------------------------------------
51904803 NA38430492 SC35658
51904806 NA38430493,NA38430492 SC35658,SC35657
-----------------------------------------------------------
代码如下:
update developer_pan01_tbl a
set (a.user_id,a.developer,a.develop_organ)=
(select user_id, wmsys.wm_concat(developer),wmsys.wm_concat(develop_organ)
from developer_pan02_tbl b
where a.user_id=b.user_id
group by user_id)