mysql update multiple records

I have a table with many fields.
The table has a field ID auto incrementing.
I tried to update a recordset as follows:
get fieldA, (AN;2018-02-01!GI;2018-03-01!ES;2018-04-02)
split it into an array (seperator “!”)
split every item of the array into a fieldname and value (seperator “;”)
update every fieldname with that value


[code]if rs<> nil then
while not rs.EOF
// get histofield
dim m9 As string = rs.Field(“ct_histo”).StringValue
// split into array of positions
dim aFldsHisto() As string = Split(m9,"!")
// read array en put data into record field
for each aFld as string in aFldsHisto
dim f1 as string = NthField(aFld,";",1)
dim f2 As string = NthField(aFld,";",2)
rs.Field(f1).StringValue = f2


end if[/code]

only the first record is updated
After rs.movenext he jumps out of the loop

Are you certain that the original rs returns more than 1 row?

I already experimented this :
you cannot make a recordset.update inside a loop of this same recordset.
the recordset is not consistent during the whole loop.
may be this work with some databases engines, but you can’t rely on it.

the solutions :

  • use the update query of all sql languages
  • get the primarykeys ( integers) of all the recordsets in your initial search query, then apply the updates on all records
    searching each time for the record identified by the unique integer.

you can use the first one on all databases. the second is not ideal for remote databases as it can really take a longer time to execute.

I was afraid this would be something like this.
But if the table will extend to multiple 100000 records, even more, this will take a long time. Not?

the sql update command is very fast, even on remote databases…

No more time than rs.Edit/rs.Update. Maybe even less.