Hi! If you don't care about exact ordering, the fastest way is UPDATE tableName SET columnName = ROWNUM();
If you want to preserve it, a slower command is needed: MERGE INTO tableName USING (SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName) FROM tableName) T(columnName, R) ON tableName.columnName = T.columnName WHEN MATCHED THEN UPDATE SET columnName = T.R; This command also requires a lot of memory (because H2 cannot buffer window functions on disk). If you have an ascending index on tableName(columnName), you can try to use more efficient version without window functions: MERGE INTO tableName USING (SELECT columnName, ROWNUM() FROM tableName ORDER BY columnName) T(columnName, R) ON tableName.columnName = T.columnName WHEN MATCHED THEN UPDATE SET columnName = T.R; Without a compatible index ROWNUM() can number rows in order different from specified in ORDER BY clause. -- 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/d1add6b0-1d5e-4194-b3fd-2f4ff96a90b0n%40googlegroups.com.
