SQL-92(Filemaker):如何更新序列号列表? [英] SQL-92 (Filemaker): How can I UPDATE a list of sequential numbers?

问题描述

在其中一个SortID更改(例如，从444更改为444.1)之后，我需要使用SQL-92从表Beleg的记录子集中重新分配所有的SortID，从1开始直到MAX(SortID).我尝试了几种方法(例如SET @a:= 0； UPDATE表SET field = @ a:= @ a + 1 WHERE what ='whatever'ORDER BY field2)，但是它没有用，因为所有这些解决方案都可以需要一种特殊的SQL，例如SQLServer或Oracle等.

I need to re-assign all SortID's, starting from 1 until MAX (SortID) from a subset of records of table Beleg, using SQL-92, after one of the SortID's has changed (for example from 444 to 444.1). I have tried several ways (for example SET @a:=0; UPDATE table SET field=@a:=@a+1 WHERE whatever='whatever' ORDER BY field2), but it didn't work, as these solutions all need a special kind of SQL, like SQLServer or Oracle, etc.

我使用的SQL是在FileMaker中实现的SQL-92(虽然可以使用INSERT和UPDATE，但是没有花哨的地方).

The SQL that I use is SQL-92, implemented in FileMaker (INSERT and UPDATE are available, though, but nothing fancy).

谢谢您的提示！

加里

推荐答案

我终于从 Ziggy那里得到了答案我的问题的数据库管理员副本上的Crueltyfree Zeitgeister .

I finally got the answer from Ziggy Crueltyfree Zeitgeister on the Database Administrators copy of my question.

他建议使用临时表将结果分为多个步骤:

He suggested to break this down into multiple steps using a temporary table to store the results:

CREATE TABLE sorting (sid numeric(10,10), rn int);

INSERT INTO sorting (sid, rn)
SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID;

UPDATE Beleg SET SortID = (SELECT rn FROM sorting WHERE sid=Beleg.SortID)
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210;

DROP TABLE sorting;

当然！我只是将表定义保留在Filemaker中(让Filemaker通过这种方式强制类型转换)，然后使用我的函数RenumberSortID()对其进行填充和删除.

Of course! I just keep the table definition in Filemaker (let the type coercion be done by Filemaker this way), and filling and deleting from it with my function: RenumberSortID ().

这篇关于SQL-92(Filemaker):如何更新序列号列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！