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. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9917f6e2-e78c-45e4-a3dc-ab3fb166e1e2%40googlegroups.com.
