MySQL: Can I update a record in a current result set or do I need 2 parallel queries/connections

I am writing a C app which retrieves a number of records using one select statement. After issuing the query, I retrieve these records using mysql_use_result then repeat mysql_fetch_row.

This is working fine, but now I want to update each record after I retrieve it. My options as I see them (but perhaps not technically possible) are:

1. Issue a second sql query to update that one record, while the first result set is still in use (But, if I issue a second query will it screw up the result set from my first query??)

2. Open another connection, then issue another sql query to update that one record. (But, can I open two connections from the same process to the same SQL server, and will is screw up the first connection?)

3. Change my first query to retrieve one record at a time, free the result set, and then issue a second query to update the record, and then repeat. This seems ineffici开发者_运维知识库ent. (Can I reuse the same connection after I mysql_free_result?)

What is possible? What is the right way to handle this?

Thanks!

You could also read all rows, remember the updates you need to make, and issue the updates afterwards. The best way might be to issue a single update statement that updates all the rows at once. Whether this is possible depends on what exactly you need to update.