Hello Dirk,
I have to disagree.
Your first update query is very low. It probably implies to run the sub
select statement for each row to be updated.
Following update statement is already much faster: (using UPDATE FROM)
update test_table set mygroup= t.mygroup from test_table as t where t.family = test_table.family and t.rang =
1 and table.rang=0 -- perform the updte only when required and mygroup <> t.mygroup;
But when you are dealing with "parent - child" relations within a
single table as in my case,
a single table scan with SELECT DISTINCT ON and a row by row comparison
on the result set appears to be faster.
I tested both approaches on tables with ca. 14'000'000 rows where 25% of
them needed to be updated.
The above update statement run in 5H30' where my function did the job in
2H.
(as my tables are very large, much time is lost in i/o wait)
Cheers,
Marc