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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/4f19eb9d-f938-4aa0-9742-81f8f3bb2754n%40googlegroups.com.
