[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