表A myid prid
a0001 1245
a0001 2546
a0002 5869
a0002 5896
a0003 5894
a0003 5897
***** ****表B
myid prid
a001
a002
a003
表A與表b通過MYID關聯,將表A的prid中的任意一條更新到表B的PRID
a0001 1245
a0001 2546
a0002 5869
a0002 5896
a0003 5894
a0003 5897
***** ****表B
myid prid
a001
a002
a003
表A與表b通過MYID關聯,將表A的prid中的任意一條更新到表B的PRID
update a set prid=(select top 1 prid from tbA where myid=a.myid) from tbB a
update b
set prid = max(prid)
from a
where a.myid = b.myid
SET prid=A.prid
FROM 表B B CROSS APPLY (SELECT TOP(1)* FROM 表A A WHERE A.myid=B.myid ORDER BY NEWID()) A
update b
set b.prid=a.prid
from a,b
where a.myid=b.myid
SET prid=A.prid
FROM 表B B CROSS APPLY (SELECT TOP(1)* FROM 表A A WHERE A.myid=B.myid ORDER BY NEWID()) A
update B set B.prid = select top 1 A.prid from A where A.MYID = B.MYID
create table ta(myid nvarchar(10),prid int)
insert into ta select 'a0001',1245
insert into ta select 'a0001',2546
insert into ta select 'a0002',5869
insert into ta select 'a0002',5896
insert into ta select 'a0003',5894
insert into ta select 'a0003',5897
create table tb(myid nvarchar(10),prid int)
insert into tb(myid) select 'a001'
insert into tb(myid) select 'a002'
insert into tb(myid) select 'a003'
go
update tb set prid=a.prid from tb b inner join ta a on stuff(a.myid,2,1,'')=b.myid
go
select * from tb
/*
myid prid
---------- -----------
a001 1245
a002 5869
a003 5894(3 行受影响)
*/
go
drop table ta,tb