List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 18 2002 7:26pm
Subject:Re: SELECT and UPDATE at the same time?
View as plain text  
At 17:56 +0000 12/18/02, Jeff Snoxell wrote:
>Hello again,
>
>I'm selecting a group of records from my database. I then loop 
>through the selected records and do some work based on what I find. 
>But what I also want to do as I interrogate each record is update 
>some of its fields with new values... but won't that screw up the 
>outer loop? I mean if I try to execute a query whilst looping around 
>the result set of a former query will I not screw up my result set 
>that I'm looping through?

No, for two reasons:

If your retrieval is based on the mysql_store_result() model, all the rows
in the result set will have been pulled to the client side by the time you
see any of them.  So when you perform your updates, you're not going to be
affecting the actual rows that you're looking at.

If your retrieval is based on the mysql_use_result() model, then you pull
each row from the server one at a time, and you *do* see it as you retrieve
it.  But this also leaves the table read-locked for the duration of the
retrieval, and so you can't update it anyway.  (And besides, the nature of
of the client/server protocol in this case prohibits you from issuing any
queries until you terminate your retrieval operation.)

mysql_store_result() and mysql_use_result() are C API functions that you
can read about in the MySQL manual.  Most other APIs are built on the C API
and some of them allow you to retrieve in one mode or the other.

>
>Also, is it possible to update specific fields of certain records 
>within a SELECT query? ie can I do something like this:
>
>SELECT * FROM my_table WHERE Age > 50 AND UPDATE Status = "OLD"

No. That's goofy anyway.  Why wouldn't you just use a regular
UPDATE query?

UPDATE mytable Status = "OLD" WHERE Age > 50;

>
>Ta,
>
>Jeff

Thread
SELECT and UPDATE at the same time?Jeff Snoxell18 Dec
  • Re: SELECT and UPDATE at the same time?Paul DuBois18 Dec
    • Re: Re: SELECT and UPDATE at the same time?Jeff Snoxell18 Dec
      • RE: Re: SELECT and UPDATE at the same time?Mike Hillyer19 Dec
  • Re: SELECT and UPDATE at the same time?Gelu Gogancea18 Dec
    • Re: SELECT and UPDATE at the same time?Paul DuBois18 Dec
  • RE: SELECT and UPDATE at the same time?Dan Cumpian19 Dec
    • RE: SELECT and UPDATE at the same time?Paul DuBois19 Dec
RE: SELECT and UPDATE at the same time?ir00038719 Dec
RE: SELECT and UPDATE at the same time?Paul DuBois19 Dec
  • RE: SELECT and UPDATE at the same time?Dan Cumpian20 Dec