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 SNxxxxx so set @high=xxxxx 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]