row_number() over (order by (select null))

[Wouter Zelle]

Hi,

I have a query that works in SQL Server, but not in H2 1.4.200:

update table set field = sub.rownumber from 

(select row_number() over (order by (select null)) as rownumber from table) sub

The error message claims that there is a syntax error after the first 'from.' Is there a way to do this that works in both SQL Server and H2?

[Evgenij Ryazanov]

Hello.

I tried to execute such command in SQL Server 2012, 2017, and 2019 on db<>fiddle and I must say that it leads to undefined results in this database system. It can set column to 1 in all rows, in can set column to 1 in some rows and to some other number in other rows depending on content of the table and I don't see any pattern.

And of course such command is not a part of the SQL Standard. H2 does not support it. Some other databases may accept it, for example, PostgreSQL accepts it and sets this column to 1 in all rows.

I don't understand what do you want. UPDATE tableName SET fieldName = 1 will be more reliable, efficient, and compatible, every normal database should accept it.

[Evgenij Ryazanov]

If you want to assign row numbers to a some column, you can use the following command in both SQL Server and H2:

CREATE TABLE TEST(ID INT PRIMARY KEY, NUMBER INT);

INSERT INTO TEST(ID) VALUES (1), (2), (5), (6), (7), (10);

WITH T(ID, RN) AS (SELECT ID, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM TEST)

MERGE INTO TEST USING T ON TEST.ID = T.ID

WHEN MATCHED THEN UPDATE SET TEST.NUMBER = T.RN;

SELECT * FROM TEST;

ID NUMBER

1 1

2 2

5 3

6 4

7 5

10 6

H2 can do it with more simple command such as UPDATE TEST SET NUMBER = ROWNUM, but SQL Server can't.

[Wouter Zelle]

Thanks a lot! It works very well.