非常棘手：無法更新順序列表 SQL-92（Filemaker）

我有一個非常簡單的問題，但解決方案非常棘手。我試圖解決這個問題幾個月：

有一列“SortID”需要使用從 1 開始的連續整數進行更新。該列可能如下所示（沒有 ORDER BY）：

SELECT SortID FROM Beleg

2
3
.5
4
5
9
10
10.000001
10.000002
11
13

並且需要看起來像這樣。

2
3
1
4
5
6
7
8
… and so on

我已經嘗試了我能想到的一切，但沒有運氣。

我正在使用 Filemaker 中的 Execute SQL 函式，我可以只使用普通的 SQL-92，沒什麼特別的。

例如，以下技巧都不起作用（例如：使用序列號更新現有行…）。都會產生語法錯誤：

ALTER TABLE MyTable ADD MyColumn int IDENTITY(1, 2) NOT NULL

和：

ALTER TABLE MyTable ADD MyColumn uniqueidentifier NOT NULL
   CONSTRAINT DF_MyTable_MyColumn
       DEFAULT (NEWSEQUENTIALID())

和（不允許使用變數）：

DECLARE @id INT 
SET @id = 0 
UPDATE X
SET @id = cn = @id + 1 
GO

並且（ROW_NUMBER () 不起作用）：

update T
set cn = rn
from (
   select cn,
       row_number() over(order by (select 1)) as rn
   from TableX
   ) T

和（這很接近，但沒有）：

update invoice set RecNo = (next value for seq_invoice_recno)
where invoiceid in (select top 100000 invoiceid from invoice where RecNo is null 
order by invoiceId)

. . . 但是，我已經接近了一點：我可以使用 Filemaker 函式“RecordNumber”，它為每一行返回一個序號。使用此語句，我可以獲得正確的順序並獲得該行的正確整數：

SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

.5  1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10  10
10.00001    11
10.00002    12

但是當我嘗試寫入值時，只要包含 ORDER BY 語句，就會出現錯誤：

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

=> 錯誤

只是，當我刪除 ORDER BY 時，會寫入值——但順序錯誤！

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210

1
2
3
4
5
6
7
8
… and so on

SortID “1” 應該寫入第 3 行，“2” 寫入第一行，“3” 寫入第二行，如下所示：

2
3
1
4
5
6
7
8
… and so on

如此接近，但仍然沒有運氣。

有人有什麼主意嗎？

非常感謝！

加里

這是另一種方法，但是，它的性能可能比其他建議更差：

UPDATE
 Beleg
SET
 SortID = (
   SELECT
     COUNT(*)
   FROM
     Beleg AS Beleg2
   WHERE
     Year ( Beleg2.Valuta ) = Year ( Beleg.Valuta )
     AND Beleg2.Ursprungskonto = Beleg.Ursprungskonto
     AND Beleg2.SortID <= Beleg.SortID
 )
WHERE
 Year ( Valuta ) = 2016
 AND Ursprungskonto = 1210
;

從好的方面來說，此方法不僅可以在單個步驟中重置SortID值，而且如果需要，它還可以一次為多個*(Year(Valuta), Ursprungskonto)*子集執行此操作。也就是說，您可以在主 WHERE 子句中為任一屬性指定多個值，例如：

WHERE
 Year ( Valuta ) BETWEEN 2011 AND 2016
 AND Ursprungskonto IN (1210, 2101, 1012)

甚至完全刪除 WHERE 子句——每個子集將與其他子集分開重新編號，所有這些都使用單個查詢。

重要說明：此方法假定SortID值在任何單個*(Year(Valuta), Ursprungskonto)*子集中都是唯一的。

引用自：https://dba.stackexchange.com/questions/135213