[thelist] SQL question
rudy
Rudy_Limeback at maritimelife.ca
Tue Dec 19 17:30:24 CST 2000
> UPDATE sample
> SET status='CONFIRMING'
> , orderid=MAX(orderid)+1
> WHERE customerid='2'
> AND orderid='-1';
>
> MySQL gives me 'ERROR 1111: Invalid use of group function'.
> I assume I can't use MAX(orderid)+1 in this context,
hi james
yes, that's right, you can't
actually it would ordinarily be possible, but it requires a subquery,
which mysql cannot handle, so i'm told (other databases are fine)
but you don't want to do it that way anyway ;o)
> but I don't want to do this in 2 sql statements,
> because I just might (however small the chance)
> get same same orderid on 2+ orders ...
good call -- you don't want to update more than one row with what's
supposed to be a unique number
however, regarding taking more than one step, if you're trying to fix your
database, like a one-time fix, then efficiency doesn't matter, so the
easiest way to do it would be in a script like this --
begin loop
find a row with the problem
fix the problem on that row
end loop
the first step is easy, you apparently have a bunch of rows with
orderid=-1, however, whether *the rows* are all unique is the question
also, i am a bit confused by your statement "pending orders always have a
orderid of '-1'" -- is that the way it has to stay or would you like to
assign all these a new unique id? i'm going to assume you will assign
unique numbers from now on...
anyhow, the tricky part is finding "a" row, because the second step, to
fix each row, requires that you can identify that row by some combination
of values of columns
for example, suppose you had the following table
id name
12 mary
13 beth
14 sue
-1 fred
-1 bob
-1 billy joe
the only thing to watch for is duplicate names -- like i said, you have to
be able to identify each row uniquely in order to fix it, and if you don't
have enough columns, you are s.o.l. and you have to dump the table and fix
it manually
in the above example, assuming the names are unique, your loop would look
something like this --
select max(orderid) as highest
from yourtable
begin loop
highest = highest + 1
select min(name) as thisname
from yourtable
where orderid=-1
update yourtable
set orderid = highest
where name = thisname
end loop
(not shown is "no results" testing inside the loop to know when you're
done)
does this help?
rudy
More information about the thelist
mailing list