Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

Question

I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months:

There is a column "SortID" that needs to get updated with sequential integers, starting from 1. The column may look like this (without ORDER BY):

SELECT SortID FROM Beleg

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

and needs to look like this.

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

I have tried everything that I can think of, but no luck.

I am using the Execute SQL function from within Filemaker and I can just use plain SQL-92, nothing fancy.

For example, the following tricks all don't work (for example: Update existing rows with sequence number…). All produce syntax errors:

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

and:

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

and (no variables allowed):

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

and (ROW_NUMBER () doesn't work):

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

and (this gets close, but no):

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)

. . . However, I have come a little closer: I can use a Filemaker function "RecordNumber" that returns a sequential number for each line. Using this statement, I can get the correct order and get the correct integer for that line:

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

But when I try to write the values, I get an error, as soon as include the ORDER BY statement:

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

=> ERROR

Only, when I remove the ORDER BY, the values are written — but in the wrong order!

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

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

The SortID "1" should have been written into line 3 and the "2" into the first line, the "3" into the second line, like this:

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

So close, but still no luck.

Does anyone have any idea?

Thanks a lot!

Gary

Answer 1

Here is another approach, which, however, is likely to perform worse than the other suggestion:

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
;

On the plus side, not only does this method reset SortID values in a single step, it can also do that for multiple (Year(Valuta), Ursprungskonto) subsets at once, if need be. That is, you can specify multiple values for either attribute in the main WHERE clause, e.g. like this:

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

or even just remove the WHERE clause altogether – and each subset will be re-numerated separately from the others, all with the single query.

Important note: this method assumes that SortID values are unique across any single (Year(Valuta), Ursprungskonto) subset.

Answer 2

If you're able to create a table, and update allows you to use the result of a subselect (all valid SQL92 code), you should be able do perform the following multi-step process:

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;

You may need to cast rn to numeric, or use a datatype other than numeric (such as double precision).