Firebird, Delphi - Need help in an efficient way of updating a table
Posted on 2009-04-02
Well, I am using 2 tables (Players+teams) and at the moment I am using a rather slow process using Listboxes etc to get the result I want. But I think I can manage to achieve it using only FB SQL commands which I would think improves the speed a lot.
What I want to achieve:
I want to select the first 6 names(field is called NAME) where Club = clubchosen.caption and Injury = 0 and Pos = D. No problems doing this using:
SELECT First 6 * FROM SPELARE where klubb = '''+clubchosencaption+'''' and Pos = 'D' and Injury = '0' order by Def DESC;
Here's basically what I need help with:
From those 6 records chosen, the first 2 needs to have its field "Lineup" updated to '1', the following two results(3+4) should have the field "Lineup" set to 2 and finally the last 2 records(5+6) should have the field "Lineup" set to 3.
At the same time, the Players table holds 2 fields called Def and Off (basically the strength of the player)
From the query mentioned above and the 6 records that shows up, the Def and Off(holds a number from 1-20) should be added and the final result should be placed in LineupDef.Caption(for Def field) and LineupOff.Caption(for Off field).
What would be the most efficient way to achieve this, especially the update method where it also needs to do some counting? (The db will only be used locally.. used Paradox before but it was way too slow when I reached 10k records).
Hope you can understand me and bare with my poor english.