一对多更新，但只更新一行
表A:
Rid productCode
1 2
2 1
A中productCode的值是唯一的
表B
id ProductCode Rid
1 2 需更新的值
2 1 需更新的值
3 2 需更新的值

更新后想得到的结果是这样的，因为下面表B productcode的值2出现了二次，但我用表A的值来更新只要更新一次，相同的就不要更新。结果如下所示：
id ProductCode Rid
1 2 1
2 1 2
3 2 null(这里表示空其它空字符也行)

望各位大侠给个实现方法。

[解决办法]
update b
set rid = a.productcode
from tb b ,ta a
where a.rid = b.productcode and not exists(select 1 from tb where productcode = b.productcode and id < b.id)
[解决办法]

SQL code

-------------------------------------- Author:%e7%b4%ab%e7%ab%b9%e6%9e%97%e7%95%94 -- Date:2012-09-11 21:36:56-------------------------------------- Test Data: tAIf object_id('tA') is not null     Drop table tAGoCreate table tA(Rid int,productCode int)GoInsert into tAselect 1,2 union allselect 2,1 Go-- Test Data: tBIf object_id('tB') is not null     Drop table tBGoCreate table tB(id int,ProductCode int,Rid int)GoInsert into tBselect 1,2,null union allselect 2,1,null union allselect 3,2,null Go--Startupdate bset rid = a.productcodefrom tb b left join ta a on a.rid = b.productcode where  not exists(select 1 from tb where productcode = b.productcode and id < b.id)select * from tb--Result:/*id          ProductCode Rid         ----------- ----------- ----------- 1           2           12           1           23           2           NULL（所影响的行数为 3 行）*/--End
[解决办法]
SQL code
;with cet1 as(select *,Row_number()over(partition by productCode  order by (select 1)) as rn from 表B)update cet1 set Rid  = b.Rid from cet1 a, 表A b  where a.productCode  = b.productCode   and rn = 1
[解决办法]
oracle没update from，更新此点，其他类似就可以了

ls几位辛苦了，呵呵
[解决办法]
SQL code
-------------------------------------- Author:%e7%b4%ab%e7%ab%b9%e6%9e%97%e7%95%94 -- Date:2012-09-11 21:36:56-------------------------------------- Test Data: tAIf object_id('tA') is not null     Drop table tAGoCreate table tA(Rid int,productCode int)GoInsert into tAselect 1,2 union allselect 2,1 Go-- Test Data: tBIf object_id('tB') is not null     Drop table tBGoCreate table tB(id int,ProductCode int,Rid int)GoInsert into tBselect 1,2,null union allselect 2,2,null union allselect 3,1,null Go--Startupdate bset rid = a.productcodefrom tb b left join ta a on a.rid = b.productcode where  not exists(select 1 from tb where productcode = b.productcode and id < b.id)select * from tb--Result:/*id          ProductCode Rid         ----------- ----------- ----------- 1           2           12           2           NULL3           1           2*/--End