RE: SELECT and UPDATE at the same time?
Well, the only basis I have is personal experience from connecting to a local MySQL database. The components I use for connecting to MySQL with Delphi does cache the records as the server returns them. However, not all the records are returned at once. As I said, it depends on how one connects to the server. If I'm wrong, then I apologize for any incorrect information that I may have given. Just trying to help. Dan Cumpian On Thu, 19 Dec 2002 00:18:48 -0600 Paul DuBois [EMAIL PROTECTED] wrote: At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT and UPDATE at the same time?
At 13:23 -0500 12/19/02, [EMAIL PROTECTED] wrote: Well, the only basis I have is personal experience from connecting to a local MySQL database. The components I use for connecting to MySQL with Delphi does cache the records as the server returns them. However, not all the records are returned at once. As I said, it depends on how one connects to the server. Okay. If that's what you observe, I won't dispute it. But then the behavior is probably something specific to the Delphi interface. The underlying behavior of the client/server protocol is somewhat different than what you observe, so people using other APIs will likely see something different happening. If I'm wrong, then I apologize for any incorrect information that I may have given. Just trying to help. Dan Cumpian On Thu, 19 Dec 2002 00:18:48 -0600 Paul DuBois [EMAIL PROTECTED] wrote: At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT and UPDATE at the same time?
Actually, I think the behavior comes from the components used to connect to MySQL. Delphi doesn't really care one way or the other. Dan Cumpian -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 1:41 PM To: [EMAIL PROTECTED] Cc: Dan Cumpian; 'Jeff Snoxell'; [EMAIL PROTECTED] Subject: RE: SELECT and UPDATE at the same time? At 13:23 -0500 12/19/02, [EMAIL PROTECTED] wrote: Well, the only basis I have is personal experience from connecting to a local MySQL database. The components I use for connecting to MySQL with Delphi does cache the records as the server returns them. However, not all the records are returned at once. As I said, it depends on how one connects to the server. Okay. If that's what you observe, I won't dispute it. But then the behavior is probably something specific to the Delphi interface. The underlying behavior of the client/server protocol is somewhat different than what you observe, so people using other APIs will likely see something different happening. If I'm wrong, then I apologize for any incorrect information that I may have given. Just trying to help. Dan Cumpian On Thu, 19 Dec 2002 00:18:48 -0600 Paul DuBois [EMAIL PROTECTED] wrote: At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT and UPDATE at the same time?
At 17:56 + 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT and UPDATE at the same time?
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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: SELECT and UPDATE at the same time?
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; Cos I want to do a fairly long-winded process on the records of those who are Age50 and subsequently update all of their records with the Date/Time that the change took place. If I do the long-winded process and _then_ do the UPDATE query as you suggest I'm bound to update records which have been added in the meantime (by other clients, it's a busy database) which I've not yet processed. I could make a list of all primary key values I've processed and then update them... or something equally ugly. I just figured there'd be a cleaner way to do it. I really want to avoid grabbing all the results as my server is not located on my local machine. Oh, I could update all the records where age50 with a known date/time then do my select query to select only those records with that time-stamp, and then do my long-winded processing. Can't help thinking there must be a nicer way to do it. Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT and UPDATE at the same time?
At 21:40 +0200 12/18/02, Gelu Gogancea wrote: Hi, You can use REPLACE...SELECT Not in the case described below. You cannot replace into the same table from which you're selecting. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 7:56 PM Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT and UPDATE at the same time?
Hi, You can use REPLACE...SELECT Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 7:56 PM Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Re: SELECT and UPDATE at the same time?
Two Ideas: First of all, the DATESTAMP datatype automatically updates it's datestamp with every modification to the row. You may want to use it to automatically update the time of your update. Also, you may want to look into using the TEMPORARY table type. Make a temporary table, fill it with the primary keys of the records you want to update by selecting into it from the main table, then iterate through those for processing, with the updates going to the main table. Mike Hillyer -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 1:54 PM To: [EMAIL PROTECTED] Subject: Re: Re: SELECT and UPDATE at the same time? 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; Cos I want to do a fairly long-winded process on the records of those who are Age50 and subsequently update all of their records with the Date/Time that the change took place. If I do the long-winded process and _then_ do the UPDATE query as you suggest I'm bound to update records which have been added in the meantime (by other clients, it's a busy database) which I've not yet processed. I could make a list of all primary key values I've processed and then update them... or something equally ugly. I just figured there'd be a cleaner way to do it. I really want to avoid grabbing all the results as my server is not located on my local machine. Oh, I could update all the records where age50 with a known date/time then do my select query to select only those records with that time-stamp, and then do my long-winded processing. Can't help thinking there must be a nicer way to do it. Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT and UPDATE at the same time?
Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT and UPDATE at the same time?
At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? 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? 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 Ta, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
