[thelist] DB increments without an auto increment

[Steve Cook]

Cheers for the idea Rudy. The only problem I can see with that is that the
increment would need to begin from the same place as the increment in the
original table stops. This would probably necessitate creating the temp
table from scratch with an autoincrement that begins at the right number. As
this is a multi-user system I'm not entirely sure whether I can prevent
anyone else from updating something else in the system during this time.
Hmmmm.


I'm guessing that your answer means that you are pretty sure there's no
"nice" way of getting the sub-select to parse on each pass of the query. In
fact I doubt there's even such as thing as separate "passes" through the
query in SQL.

Oh well, back to the drawing board!


------------------------------------------
Steve Cook
web strategist
Evitbe AB
031-15 16 17   031-809 365   0703-13 26 31
steve.cook at evitbe.com       www.evitbe.com
------------------------------------------

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: den 8 april 2002 17:58
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] DB increments without an auto increment
>
>
> > The database has an ID field, but no autoincrement, instead
> > new ID's are generated programmatically. Normally this is no
> > major problem (SELECT MAX(ID)+1 will do the trick)
>
> hi steve
>
> just a guess, but can we assume you don't want to hear how
> dangerous that
> is?
>
> > INSERT INTO Logg (ID, cardNum, persName)
> >  SELECT (SELECT MAX(ID) FROM Logg)+1
> >         , relMailTemp.cardNum, relMailTemp.Name
> >    FROM relMailTemp
>
> one approach is to insert them into a temp table that *does* have an
> autoincrement field, and then insert the temp rows into the final Logg
> table
>
>    insert into temptable (cardNum, persName)
>        select cardNum, Name
>          from relMailTemp
>
>   insert into Logg (ID, cardNum, persName)
>      select ID, cardNum, Name
>          from temptable
>
> you would probably also want to clear out the temp table
> before or after
> using it, and surround all three steps in a transaction block
>
> rudy
>