Updating table with query results

[kamil.jedrzejewski]

I have one large table Tab1 on which I'm doing some very time-
consuming calculations (by query).
I can only add new data to this table, older date can't be changed.
But on daily basis new data is appended to this table, and should also
be recalculated.
I think that it will work faster, if I do my calculations just one
time and assign them to my table (as additional column).

Example
Tab1
ID::field1
1::a
2::b
3::a
4::a

Result:
Tab1_ver2
ID::field1::newcolumn
1::a::1
2::b::1
3::a::2
4::a::3

My calculation is to create from field f1 second key by adding counter
to it.
Pieter Wijnen helped me with numbering solution:
SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND
P1.FIELD1=P.FIELD1)
AS ROWNUM, P.*
FROM P

Now I want to add this ROWNUM field to my table. Is it possible?

Thanks for help
Kamil

 

[kamil.jedrzejewski]

I have one large table Tab1 on which I'm doing some very time-
consuming calculations (by query).
I can only add new data to this table, older date can't be changed.
But on daily basis new data is appended to this table, and should also
be recalculated.
I think that it will work faster, if I do my calculations just one
time and assign them to my table (as additional column).

Example
Tab1
ID::field1
1::a
2::b
3::a
4::a

Result:
Tab1_ver2
ID::field1::newcolumn
1::a::1
2::b::1
3::a::2
4::a::3

My calculation is to create from field f1 second key by adding counter
to it.
Pieter Wijnen helped me with numbering solution:
SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND
P1.FIELD1=P.FIELD1)
AS ROWNUM, P.*
FROM P

Now I want to add this ROWNUM field to my table. Is it possible?

Thanks for help
Kamil

I just realized that query updating the table which is searched by
this query is quite senseless...
what about creating 2nd table (key conversion table) with the key from
source table and value returned by query?
Tab2
ID::newcolumn
1::1
2::1
3::2
4::3

There should be an append query (running after database refresh) which
will add new records based on ID from Tab1 and newcolumn based on
query (SELECT by Pieter) (query based on Tab1 too).
Is it possible to do that automatically, w/o any prompts?

Regards