使用CTE和NEWID()更新表 [英] Update a table using CTE and NEWID()
本文介绍了使用CTE和NEWID()更新表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想尝试使用同一张表中的随机值更新一张表,但是原始表没有要遵循的标识或任何其他数字列...
I want try update a table using random values from the same table, but the original table don´t have a identity to follow or any other number column...
WITH cteTable1 AS (
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
ENDE_NO_Address
FROM TableX
)
UPDATE TableX SET ENDE_NO_Address = (
SELECT ENDE_NO_Address
FROM cteTable1
WHERE cteTable1.n = This is the problem...
寻求帮助
推荐答案
declare @T table(Col1 varchar(10))
insert into @T values (1),(2),(3),(4),(5)
;with cte as
(
select *,
row_number() over(order by newid()) as rn1,
row_number() over(order by Col1) as rn2
from @T
)
update C1 set
Col1 = C2.Col1
from cte as C1
inner join cte as C2
on C1.rn1 = C2.rn2
<力量ong>编辑:
WITH cte AS (
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS n1,
ROW_NUMBER() OVER (ORDER BY (select 1)) AS n2,
ENDE_NO_Address
FROM TableX
)
update C1 set
ENDE_NO_Address = C2.ENDE_NO_Address
from cte as C1
inner join cte as C2
on C1.n1 = C2.n2
这篇关于使用CTE和NEWID()更新表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文