[thelist] SQL: insert into mutiple tables help

Warden, Matt mwarden at odyssey-design.com
Tue Apr 10 18:02:47 CDT 2001

Allow me to take a deep breath before defying the master...

> which leads some people to doing the insert followed by
>    select max(id) from thetable
> but this can give back the wrong value under circumstances of concurrent
> users
> [tip]
> to discourage yourself from ever being tempted to use select max(id) on an
> autonumber, define it random
> [/tip]

Boo! (see below)

> what you have to do is save another field's value in a variable, do the
> insert, then run a query to get the autonumber that was assigned
>   select id from thetable
>      where theotherfield = 'value'
> and then use this id as the foreign key in the insert of the child record

Hmmmm... or you can just define it as an incrementing autonumber and use:

  select max(id) from thetable
     where email = 'joe at public.com';

Then, you don't have to be checking to see if joe at public.com already exists.
If the user submits the form twice in a row or something else that causes
concurrent requests with the same email address, who cares? You'll get one
of them. The only bad thing is that you have an dupe record that won't be
used (assuming you don't have to search on email after you have the id).

And, this isn't just for email addresses.

though, rudy's thought could be more applicable in other situations where
two different users could supply the same information and you're using that
information to get back the id. However, I can't think of an example where
that would be the case.


More information about the thelist mailing list