stepping through table rows

[Loomis]

How do you step through each row of a MySQL table to perform an update. I want to start with the first row and then iterate through the table row by row and make possible field updates. I know how to update, I don't know how to step through each row, beginning to end. This used to be simple in Foxpro. Am I overlooking something simple. Thanks in advance.

 

[Merlijn]

In human language, because I don't have an example at hand.

Define a recordset and a "SQL select" command and open it.
Check for the recordset NOT to be ".BOF AND .EOF", which would mean there are no records.
Use movefirst to go to the first record of the recordset.
Loop over the records in the recordset with the following:
Do whatever you need to do on the record.
Use movenext on the recordset to go to the next record.
if not EOF on the recordset continue the loop. If EOF is reached, end the loop.
Close the recordset.

Don't forget to run a test on a test database. Never test on a production database, since on tiny mistake might ruin a whole lot of work...

 

[Merlijn]

I made a small example:

Set rs_Recordset = New ADODB.Recordset

str_Sql = "select * from tbl_MyTable"
str_Sql = str_Sql & " where (selectData = '" & !MyDbField & "')"
rs_Recordset.Open str_Sql, cn_MyDataBase, adOpenDynamic, adLockOptimistic
If Not (rs_Recordset.BOF And rs_Recordset.EOF) Then
    rs_Recordset.MoveFirst
    While Not rs_Recordset.EOF
        If MyCondition = true Then
            rs_Recordset!MyFieldToChange = NewValue
        End If
        rs_Recordset.Update
        rs_Recordset.MoveNext
    Wend
End If
Set rs_Recordset = Nothing

Of course you need to change all the database references, the selectData and the NewValue to your situation.

 

[Andrzejek]

It may not be obvious to some, but Merlijn's example was made in VB6(Classic)/VBA (Excel or any other Office app)

 

[Merlijn]

It's VB6 Pro from the Visual Studio.

 

[mikrom]

Although the OP has not specified in this thread which programming language he is using in conjunction with MySQL database, from an earlier thread it appears that he is using Python:

Stepping through a MySQL table - Python

I am new to Python, coming from Foxpro and C#. I have 300,000 names in a MySQL table sorted alphabetically by last name then first name. I need to be able to find a name and then scroll up or down to view similar names. I am using Python and MySQL. In Foxpro, I would execute a SEEK command...

www.tek-tips.com