[thelist] SQL question

[James Spahr]

How can I (safely) set a table column to be a unique id number?

I'd just like to set all the columns that satisfy this WHERE clause to be
one more than the MAX value of the column. Like so:

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, 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 ...

(pending orders always have a orderid of '-1')

make sense?

James.