Simple MySQL help needed...
Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple MySQL help needed...
Phill, I think the solution is simple, remove the part of the code you are having problems with, it's redundant. Instead, according to your statement, use this: UPDATE Ops SET [EMAIL PROTECTED] WHERE newphoto=1 AND tbprinted=1; As for myself, I would actually use the client implementing the statement to prepare the variables in advance. Your select statement should return 0 or more records. Assuming it has 1 or more returned, simply obtain the high value to a client stored variable, add one to it, and parse it as part of the UPDATE statement, instead of having it done on the SQL side. While it may or may not offer a benefit in a minor case like this, preparing your statements ahead of time on the client side for more complicated statements can alleviate some of the processing the MySQL server has to do. If I was using C, for a simple example, it would look as follows: char pSQL[256]; int iHighNum = ObtainHighestFromDB(void); iHighNum++; snprintf(pSQL, 256, UPDATE Ops SET cardnumb=%d WHERE newphoto=1 and tbprinted=1;, iHighNum); This in effect leaves any processing possible to the client side. Hope that helps, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 1:53 PM Subject: Simple MySQL help needed... Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple MySQL help needed...
Shane Bryldt wrote: I think the solution is simple, remove the part of the code you are having problems with, it's redundant. Instead, according to your statement, use this: UPDATE Ops SET [EMAIL PROTECTED] WHERE newphoto=1 AND tbprinted=1; Thanks for the response Shane. I don't think I explained my statement clearly as your corrected code would set all matching rows to be @high+1. What I am trying to achieve is if there are 20 matching rows to the WHERE clause, then I want cardnumb to be set to @high+1, @high+2, @[EMAIL PROTECTED] respectively for the 20 records (the order they are set is irrelevant). Is it possible to have an auto-incrementing variable in an update statement like this? P h i l l As for myself, I would actually use the client implementing the statement to prepare the variables in advance. Your select statement should return 0 or more records. Assuming it has 1 or more returned, simply obtain the high value to a client stored variable, add one to it, and parse it as part of the UPDATE statement, instead of having it done on the SQL side. While it may or may not offer a benefit in a minor case like this, preparing your statements ahead of time on the client side for more complicated statements can alleviate some of the processing the MySQL server has to do. If I was using C, for a simple example, it would look as follows: char pSQL[256]; int iHighNum = ObtainHighestFromDB(void); iHighNum++; snprintf(pSQL, 256, UPDATE Ops SET cardnumb=%d WHERE newphoto=1 and tbprinted=1;, iHighNum); This in effect leaves any processing possible to the client side. Hope that helps, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 1:53 PM Subject: Simple MySQL help needed... Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple MySQL help needed...
Ahh, yes, thank you for clarifying. My code would obviously have been no help. Hmm, I have not tested the theory, but I believe you could add to your SELECT statement, a declaration of the return and use it in the next INSERT. That is, SELECT @high:=convert(...) AS a FROM Ops; And then a refers to a record set populated as you needed. I am not really buff on my SQL syntax, because I typically do most processing client side, but I believe you could then set a temporary value to high, and through a loop of the records in a, increment the temp value, before assigning it to cardnum. I understand the logic, unfortunately my SQL knowledge is still a bit rusty. If it cannot be achieved with a temp value and loop on the MySQL side, the C code for it would be quite easy. However, it would involved a multiple UPDATE calls, using a transaction most likely. Lock tables too, for efficiency. Best of luck, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 3:54 PM Subject: Re: Simple MySQL help needed... Shane Bryldt wrote: I think the solution is simple, remove the part of the code you are having problems with, it's redundant. Instead, according to your statement, use this: UPDATE Ops SET [EMAIL PROTECTED] WHERE newphoto=1 AND tbprinted=1; Thanks for the response Shane. I don't think I explained my statement clearly as your corrected code would set all matching rows to be @high+1. What I am trying to achieve is if there are 20 matching rows to the WHERE clause, then I want cardnumb to be set to @high+1, @high+2, @[EMAIL PROTECTED] respectively for the 20 records (the order they are set is irrelevant). Is it possible to have an auto-incrementing variable in an update statement like this? P h i l l As for myself, I would actually use the client implementing the statement to prepare the variables in advance. Your select statement should return 0 or more records. Assuming it has 1 or more returned, simply obtain the high value to a client stored variable, add one to it, and parse it as part of the UPDATE statement, instead of having it done on the SQL side. While it may or may not offer a benefit in a minor case like this, preparing your statements ahead of time on the client side for more complicated statements can alleviate some of the processing the MySQL server has to do. If I was using C, for a simple example, it would look as follows: char pSQL[256]; int iHighNum = ObtainHighestFromDB(void); iHighNum++; snprintf(pSQL, 256, UPDATE Ops SET cardnumb=%d WHERE newphoto=1 and tbprinted=1;, iHighNum); This in effect leaves any processing possible to the client side. Hope that helps, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 1:53 PM Subject: Simple MySQL help needed... Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
