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.