t-sql：按照“in”确定的特定顺序更新表达

发布于 2024-12-12 04:37:05 字数 260 浏览 0 评论 0原文

假设：

declare @counter int
set @counter = 0

update MyTable
set @counter = MyField = @counter + 1
where Some_ID in (select ID from SomeTable)

我想要的是更新发生的顺序与返回 select ID from SomeTable ID 的顺序相同。有没有一个好的方法来做到这一点？

原文

Suppose:

declare @counter int
set @counter = 0

update MyTable
set @counter = MyField = @counter + 1
where Some_ID in (select ID from SomeTable)

What I want is that update happened in same order as select ID from SomeTable IDs are returned. Is there a nice way to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花海 2024-12-19 04:37:05

以下是使用带有 ROW_NUMBER 的 CTE 的解决方案...您可以确定希望 SomeTable 中的记录在 OVER 子句中返回的顺序代码>ROW_NUMBER。

;WITH rn (ID, rowNumber) AS
(
    SELECT st.ID, ROW_NUMBER() OVER (ORDER BY st.ID)
    FROM SomeTable st
    WHERE EXISTS (
        SELECT 1
        FROM MyTable mt
        WHERE mt.Some_ID = st.ID
    )   
)
UPDATE mt
SET MyField = rowNumber
FROM 
    MyTable mt 
    JOIN rn ON rn.ID = mt.Some_ID

Here's a solution using a CTE with ROW_NUMBER... you determine the order that you want the records from SomeTable returned in the OVER clause of ROW_NUMBER.

;WITH rn (ID, rowNumber) AS
(
    SELECT st.ID, ROW_NUMBER() OVER (ORDER BY st.ID)
    FROM SomeTable st
    WHERE EXISTS (
        SELECT 1
        FROM MyTable mt
        WHERE mt.Some_ID = st.ID
    )   
)
UPDATE mt
SET MyField = rowNumber
FROM 
    MyTable mt 
    JOIN rn ON rn.ID = mt.Some_ID

回复收藏 0 原文

眼眸 2024-12-19 04:37:05

假设您希望在更新的记录中有一列包含递增的数字，我认为唯一的方法是获取 ID 列表并进行多次更新。

在 SQL Server 中，您可能会使用游标来执行此操作。

DECLARE @id int, @counter int
SET @counter = 0
DECLARE ids CURSOR
  FOR SELECT ID FROM SomeTable

OPEN ids
FETCH NEXT FROM ids INTO @id

WHILE @@FETCH_STATUS == 0
BEGIN
  UPDATE MyTable SET MyField = @counter WHERE Some_ID = @id
  SET @counter = @counter + 1
  FETCH NEXT FROM ids INTO @id
END

CLOSE ids
DEALLOCATE ids

Assuming you want to have a column containing an incrementing number in the updated records I think the only way you could do this is to get the list of IDs and do a number of updates.

In SQL server you would likely do this using a cursor.

DECLARE @id int, @counter int
SET @counter = 0
DECLARE ids CURSOR
  FOR SELECT ID FROM SomeTable

OPEN ids
FETCH NEXT FROM ids INTO @id

WHILE @@FETCH_STATUS == 0
BEGIN
  UPDATE MyTable SET MyField = @counter WHERE Some_ID = @id
  SET @counter = @counter + 1
  FETCH NEXT FROM ids INTO @id
END

CLOSE ids
DEALLOCATE ids

回复收藏 0 原文

~没有更多了~