SQL-92 (Filemaker): How can I UPDATE a list of sequential numbers?

Question

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.

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

Thanks for any hint!

Gary

Answer 1

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;

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 ().