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]
