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

 

[spamjim]

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.

Yes, you are overlooking something simple. When you run UPDATE, it automatically steps through each row if you have not limited the statement or added a condition that restricts the update to one row.

It may help to further explain your intended update.

 

[rashmibala]

You're not alone—coming from something like FoxPro, where row-by-row processing is built-in and intuitive, MySQL can feel a bit different since it's set-based rather than row-based. SQL, especially in MySQL, isn't designed to iterate through rows in the same procedural way as older desktop databases. However, there are a few ways you can achieve similar behavior depending on your use case:

If possible, try to write a single UPDATE statement that applies your logic to all applicable rows at once:
UPDATE my_table
SET some_column = 'new_value'
WHERE some_condition;

This is faster, more efficient, and the intended way of working with SQL.

If You Must Step Through Row by Row (Procedural Style)
DELIMITER //

Use a Stored Procedure with a Cursor​

Cursors let you loop through rows one at a time in MySQL:

CREATE PROCEDURE update_rows()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id_val INT;

-- Declare the cursor
DECLARE cur CURSOR FOR
SELECT id FROM my_table ORDER BY id;

-- Declare continue handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Open the cursor
OPEN cur;

read_loop: LOOP
FETCH cur INTO id_val;
IF done THEN
LEAVE read_loop;
END IF;

-- Perform your update here
UPDATE my_table SET some_column = 'updated value' WHERE id = id_val;

END LOOP;

CLOSE cur;
END //

DELIMITER ;