Re: Replacing Sequences

[Dustin Anderson]

Sure!

Let's say I have 2 tables:

MYCUS:
MCID NUMBER(8)
MCNAME VARCHAR2(30)


MYCUSADD:
MAID NUMBER(8)
MAMCID NUMBER(8)
MAADD VARCHAR2(60)

Oracle syntax, I know, but it's what I know off the top of my head. Now let's say that I want to insert a record into table MYCUS, and have MCID be a sequenced/auto_incremented number, but I want to insert a new record into MYCUSADD with it's OWN unique id (MAID), and the id of the record in MYCUS that it points to(MAMCID).

With Oracle I would create a SEQUENCE, and then select NEXTVAL from that sequence, and use that as the value for MCID and MAMCID, but MySQL has no such thing. If I use AUTO_INCREMENT, how do I get back the value that it assigns to insert it into MAMCID?

Does that make sense?

I saw somewhere that you can select the last incremented value from a table, but it also says that there's no guarantee that it's the value of YOUR insert as somebody could easily insert a record during the time it took you to query it, so that's not a good solution.

Thanks for your help!

Rick