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 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]
