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]
