thank you very much :-) On Sunday, 18 December 2022 at 19:47:59 UTC+8 Evgenij Ryazanov wrote:
> 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/4f19eb9d-f938-4aa0-9742-81f8f3bb2754n%40googlegroups.com.
