I have the following snippet which makes changes to the Contribution table based on a first or last name being changed in the Members table. Right now the code takes maybe as long as 7-8 minutes to make all of the updates to Contributions. If I make the change in SQLite Expert, the change is almost immediate, so I am sure that there is a better way to do it...I just don't know the best way to do it.
Thanks for your help,
'Update all contributions with head of house name change
slSel "Select * FROM Contributions WHERE FamilyID = '" & Trim$(CurrentFamilyID$) & "'", %rs1
Do While slGetRow(%rs1)
slExe "Begin Immediate"
SQL$ = "UPDATE Contributions SET Last = '" & Trim$(NewLast$) & "', First = '" & Trim$(NewFirst$) & "' WHERE FamilyID = '" & Trim$(CurrentFamilyID$) & "'"
I don't understand why you are using a select statement and a loop. You should be able to execute the UPDATE statement as written one time and it will update all records where the FamilyID index = CurrentFamilyID
Assuming the new names are stored in another table with the CurrentFamilyID as an index, there really shouldn't be any need to keep a duplicate copy of that data in the Contributions table.
Quote from: Bern Ertl on January 04, 2013, 02:34:34 pm
I don't understand why you are using a select statement and a loop.
Cause I don't know what I am doing LOL!
You should be able to execute the UPDATE statement as written one time and it will update all records where the FamilyID index = CurrentFamilyID
If that is the case, then that is probably why it is so slow. It is updating approx. 400 records in one of my test, but I assumed I needed to loop through all records that needed changed. If I can do that with one call, I like that a whole lot better. Sounds like my code may be looping through 400 records 400 times possibly.
I will give this a try.
Thanks for your help,
lol. Sorry Gary. I totally grok your situation. I'm often fumbling around myself. That's what these discussion forums are for. Cheers.
I tried removing the SELECT statement and the loop and just used the UPDATE statement and all is working as fast as possible!
Thanks for pointing me in the right direction.