22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb1(ID0 int,ID1 int)
INSERT INTO tb1 (
ID0,ID1
) SELECT 0,0 FROM sysobjects s1
DECLARE @tb2 TABLE(ID2 INT)
INSERT INTO @tb2 (
ID2
)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UPDATE tb1 SET ID1=(SELECT TOP 1
ID2
FROM @tb2
ORDER BY NEWID())
CREATE TABLE tb1(ID0 int IDENTITY,ID1 int) --id0加上identity方便查看效果
INSERT INTO tb1 (
ID1
) SELECT 0 FROM sysobjects s1
DECLARE @tb2 TABLE(ID2 INT)
INSERT INTO @tb2 (
ID2
)
SELECT 1
UNION ALL
SELECT 10
UNION ALL
SELECT 3
UNION ALL
SELECT 5
UNION ALL
SELECT 75
;WITH Liang AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY NEWID()) AS rowid FROM tb1
)
UPDATE A SET
ID1 = B.ID2
FROM Liang AS A
JOIN (SELECT rowid=ROW_NUMBER() OVER(ORDER BY NEWID())-1 ,
COUNT(*) OVER() AS maxID2,ID2 FROM @tb2) AS B
ON A.rowid % B.maxID2 = B.rowid
select * from tb1
drop table tb1
CREATE TABLE tb1(ID0 int,ID1 int)
INSERT INTO tb1 (
ID0,ID1
) SELECT 0,0 FROM sysobjects s1
UPDATE tb1 SET ID1=ABS(cast(ceiling(RAND(CHECKSUM(NEWID()))*10)-5 as int))
select * from tb1
ID0 ID1
----------- -----------
0 3
0 3
0 5
0 4
0 2
0 1
0 5
0 4
0 1
0 2
0 4
0 3
0 4
0 0
0 2
0 3
0 0
0 1
0 3
0 1
0 3
0 5
0 2
0 1
0 3
0 2
0 2
0 1
0 0
0 2
0 5
0 3
0 5
0 5
0 0
0 1
0 1
0 1
0 4
0 0
0 4
0 4
0 2
0 1
0 4
0 0
0 0
0 1
0 3
0 0
0 2
0 4
0 2
0 0
0 4
0 3
0 1
0 2
0 3
0 5
0 2
0 0
0 3
0 2
0 5
0 2
0 1
0 2
0 1
0 5
0 1
0 2
0 1
0 2
0 5
0 0
0 2
0 1
0 2
0 1
0 0
0 0
0 1
0 5
0 3
0 3
0 4
0 0
0 4
0 2
0 1
0 2
0 1
0 2
0 3
0 1
0 2
0 2
0 2
0 3
(100 行受影响)
CREATE TABLE tb1(ID0 int,ID1 int)
GO
INSERT INTO tb1 (
ID0,ID1
)
SELECT 0,0 FROM sysobjects s1
GO
CREATE TABLE tb2(ID2 INT)
GO
INSERT INTO tb2 (
ID2
)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
GO
UPDATE tb1 SET ID1=(SELECT TOP 1
ID2
FROM tb2
ORDER BY NEWID())
SELECT * FROM TB1
/**
第一次执行是2,第二次执行是4,第三执行是1
**/
DROP TABLE TB1,TB2
select @@version
/**
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
(所影响的行数为 1 行)
**/
CREATE TABLE tb1(ID0 int,ID1 int)
INSERT INTO tb1 (
ID0,ID1
) SELECT 0,0 FROM sysobjects s1
UPDATE tb1 SET ID1=ABS(cast(ceiling(RAND(CHECKSUM(NEWID()))*10)-5 as int))
select * from tb1
ID0 ID1
----------- -----------
0 3
0 3
0 5
0 4
0 2
0 1
0 5
0 4
0 1
0 2
0 4
0 3
0 4
0 0
0 2
0 3
0 0
0 1
0 3
0 1
0 3
0 5
0 2
0 1
0 3
0 2
0 2
0 1
0 0
0 2
0 5
0 3
0 5
0 5
0 0
0 1
0 1
0 1
0 4
0 0
0 4
0 4
0 2
0 1
0 4
0 0
0 0
0 1
0 3
0 0
0 2
0 4
0 2
0 0
0 4
0 3
0 1
0 2
0 3
0 5
0 2
0 0
0 3
0 2
0 5
0 2
0 1
0 2
0 1
0 5
0 1
0 2
0 1
0 2
0 5
0 0
0 2
0 1
0 2
0 1
0 0
0 0
0 1
0 5
0 3
0 3
0 4
0 0
0 4
0 2
0 1
0 2
0 1
0 2
0 3
0 1
0 2
0 2
0 2
0 3
(100 行受影响)
CREATE TABLE tb1(ID0 int IDENTITY,ID1 int) --id0加上identity方便查看效果
INSERT INTO tb1 (
ID1
) SELECT 0 FROM sysobjects s1
DECLARE @tb2 TABLE(ID2 INT)
INSERT INTO @tb2 (
ID2
)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
;WITH Liang AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY NEWID()) AS rowid FROM tb1
)
UPDATE A SET
ID1 = B.ID2
FROM Liang AS A
JOIN (SELECT rowid=ROW_NUMBER() OVER(ORDER BY NEWID())-1 ,
MAX(ID2) OVER() AS maxID2,ID2 FROM @tb2) AS B
ON A.rowid % B.maxID2 = B.rowid
select * from tb1
drop table tb1
UPDATE tb1 SET ID1=ID2
WHERE
ID1 =(SELECT
TOP 1
ID1
FROM @tb2
ORDER BY NEWID())