如何随机更新多条记录

[qq43695548]

就是将表中某个字段更新为随机（从另外一个表获得）的数字
如表 A

Aid aa
1 0
2 0
3 0

表B

Bid bb
1 1
2 2
3 3
4 4
5 5
6 6
7 7

更新后
表A变成
Aid aa
1 4
2 1
3 5

这个样子的，表A表B的数据大概都有100多条
怎样写SQL效率最高？

[bancxc]

update A Set AA=(Select top 1 BB from B Order by newid())

[永生天地]

[Quote=引用 1 楼 bancxc 的回复:]
SQL code
update A Set AA=(Select top 1 BB from B Order by newid())
[/Quote]
差不多

[bancxc]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7

update A Set AA=B.BB
from A
join(select row_number() over(order by newid()) id ,BB from B ) B on A.Aid=B.ID

select * from a
 
 

[freetd]

[Quote=引用 2 楼 xys_777 的回复:]

引用 1 楼 bancxc 的回复:
SQL code
update A Set AA=(Select top 1 BB from B Order by newid())

差不多
[/Quote]
差不多

[liyaohui13098452233]

呵呵……

[luoyoumou]

create table a(aid int, aa int);
create table b(bid int, bb int);

insert into a(aid, aa)
select
1,0 union all select 
2,0 union all select
3,0;

insert into b(bid,bb)
select
1,1 union all select
2,2 union all select
3,3 union all select
4,4 union all select
5,5 union all select
6,6 union all select
7,7;

update a
  set a.aa = b1.bb
from a inner join (select row_number() over(order by newid()) as bid, bb from b ) b1
          on a.aid=b1.bid;

select * from a;

[qq43695548]

[Quote=引用 1 楼 bancxc 的回复:]
SQL code
update A Set AA=(Select top 1 BB from B Order by newid())
[/Quote]

这个是不行的，我要的是结果字段各条记录不能完全相同

[htl258_Tony]

回的都差不多，两个表都只有一百多条就随便了，有索引计划也不会考虑使用。

[bancxc]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7

update A Set AA=B.BB
from A
join(select row_number() over(order by newid()) id ,BB from B ) B on A.Aid=B.ID

select * from a
(3 行受影响)
Aid         aa
----------- -----------
1           7
2           2
3           4

(3 行受影响)



--AA可重复
update A1 set A1.AA=B.BB from 
A A1 cross apply(
select top 1 Aid,B.BB from a,B where A.Aid=A1.AID
order by newid()
) B

select * from A


(3 行受影响)
Aid         aa
----------- -----------
1           2
2           2
3           5

(3 行受影响)

[htl258_Tony]

[Quote=引用 7 楼 qq43695548 的回复:]
引用 1 楼 bancxc 的回复:
SQL code
update A Set AA=(Select top 1 BB from B Order by newid())


这个是不行的，我要的是结果字段各条记录不能完全相同
[/Quote]3L可以

[qq43695548]

row_number是2005的

如果是2000呢？

[htl258_Tony]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
go
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
go
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7

update A Set AA=(Select top 1 BB from B WHERE a.Aid<=b.Bid Order by newid())

select * from a
 
/*
Aid         aa
----------- -----------
1           7
2           6
3           5

(3 行受影响)
*/

1楼的这样改一下也没问题。

[htl258_Tony]

update A Set AA=(Select top 1 BB from B WHERE a.Aid<>b.Bid Order by newid())

如果ID非常无序，改为这样处理。

[黄_瓜]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7

--2000
if object_id('tempdb..#') is not null drop table #
go
create table #(id int identity ,bb int)
insert into #(bb) select  bb from (select distinct bb from b) t order by newid()

update a set aa=bb from a,# t where a.aid=t.id

select * from a


drop table #
drop table a
drop table b

[qq43695548]

行了，多谢各位，马上结贴