SQL UPDATE 行号 [英] SQL UPDATE row Number

问题描述

我有一个带有列标识(1590 个值)的表 serviceClusters.然后我有另一个表 serviceClustersNew ，其中包含列 ID、文本和注释.在这个表中，我有一些文本和评论的值，ID 总是 1.这里是一个表的例子:

I have a table serviceClusters with a column identity(1590 values). Then I have another table serviceClustersNew with the columns ID, text and comment. In this table, I have some values for text and comment, the ID is always 1. Here an example for the table:

[1, dummy1, hello1;

[1, dummy1, hello1;

1、dummy2、hello2；

1, dummy2, hello2;

1、dummy3、hello3；

1, dummy3, hello3;

等]

我现在想要列 ID 中的值是表 serviceClusters 的连续索引加上当前行号:在我们的例子中，这将是 1591、1592 和 1593.

WhaI want now for the values in the column ID is the continuing index of the table serviceClusters plus the current Row number: In our case, this would be 1591, 1592 and 1593.

我试图解决这样的问题:首先我用最大值更新了列 ID，然后我尝试添加行号，但这不起作用:

I tried to solve the problem like this: First I updated the column ID with the maximum value, then I tryed to add the row number, but this doesnt work:

-- Update ID to the maximum value 1590
UPDATE serviceClustersNew 
SET ID = (SELECT MAX(ID) FROM serviceClusters);
-- This command returns the correct values 1591, 1592 and 1593
SELECT ID+ROW_NUMBER() OVER (ORDER BY Text_ID) AS RowNumber 
FROM serviceClustersNew
-- But I'm not able to update the table with this command
UPDATE serviceClustersNew 
SET ID = (SELECT ID+ROW_NUMBER() OVER (ORDER BY Text_ID) AS RowNumber FROM
serviceClustersNew)

通过发送最后一个命令，我收到错误语法错误:子查询中不允许使用有序分析函数.".你有什么建议，我该如何解决这个问题?我知道我可以使用易失性表或通过添加列来实现，但是有没有办法不创建新表/更改当前表?

By sending the last command, I get the error "Syntax error: Ordered Analytical Functions are not allowed in subqueries.". Do you have any suggestions, how I could solve the problem? I know I could do it with a volatile table or by adding a column, but is there a way without creating a new table / altering the current table?

推荐答案

你必须使用 UPDATE FROM 重写它，语法有点笨重:

You have to rewrite it using UPDATE FROM, the syntax is just a bit bulky:

UPDATE serviceClustersNew
FROM
 (
   SELECT text_id,
      (SELECT MAX(ID) FROM serviceClusters) +
      ROW_NUMBER() OVER (ORDER BY Text_ID) AS newID 
   FROM serviceClustersNew
 ) AS src
SET ID = newID
WHERE serviceClustersNew.Text_ID = src.Text_ID

这篇关于SQL UPDATE 行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！