>>> I didn't say anything at all about a separate table.
>>>
>>> The method I mentioned is quite clean. One table, you simply insert
>>> the entire row with nothing in the other fields to get it to give you
>>> the auto-incremented value then use said value to key the update.
>>
>>Ok. In my case, the table is for storing many different settings &
>>configuration options. It has the following columns: Category, Item &
>>Value. Most of the records in the table have nothing to do with unique
>>values, but a few of them do. E.g. Category = ControlNumbers, Item =
>>LastPONumber, Value = 1000
>>
>>If user A and user B simultaneously request the next invoice number, one
>>should get 1001, the other 1002 and the LastPONumber field will be 1002
>>afterwards.

1. lock tables configuration_settings write;
2. update configuration_settings set Value=Value+1
where Category = 'ControlNumbers' and Item = 'LastPONumber';
3. select Value from configuraton_settings
where Category = 'ControlNumbers' and Item = 'LastPONumber';
/* Use Value as your PO number */
4. unlock tables;

If User A is in query 2 or 3, User B will wait at query 1 until User A
finishes query 4.

>>If I understand your approach correctly, I would either have to add an
>>auto-incremented field to this table, or have an extra table just for this
>>purpose.
>>
>
>Right now you do a select for the "LastPONumber",

No, right now he does a select for Value where Category = 'ControlNumbers' and
Item = 'LastPONumber'.

>add one to it and
>insert it. CHange the LastPONumber to autoincrementing.

You can't change Value in only one row to autoincrementing.

>Problem
>solved.

There's a lot of utility in a Keyword = Value approach. For instance,
for an address book application, you might have a table: PersonID,
Attribute, and Value. Attribute might be things like 'First Name',
'Birth Date', 'Home Fax Number', 'Work Email', 'Home Street Address',
etc. You can add more without having to change the schema.
The trouble is, what type is Value? It's got dates, names,
telephone numbers, email addresses, etc. in it. You also end up
doing a lot of joins to get specific attributes (if present) in
specific variables.

Gordon L. Burditt