Re: Please help with Inserting ID into table from another table in Oracle 9.2

On Apr 30, 3:08 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Apr 30, 12:38 pm, MrHelpMe <clintto..._at_hotmail.com> wrote:
>
>
>
>
>
> > On Apr 30, 12:09 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > MrHelpMe wrote:
> > > >> Why two procedures and not one?
> > > >> --
> > > >> Daniel A. Morgan
> > > >> University of Washington
> > > >> damor..._at_x.washington.edu
> > > >> (replace x with u to respond)
> > > >> Puget Sound Oracle Users Groupwww.psoug.org-Hidequotedtext -
>
> > > >> - Show quoted text -
>
> > > > Hi DA Morgan,
> > > > Thanks for the reply. I used 2 procedures originally because I wasn't
> > > > sure how to accomplish this in 1 procedure. To be honest I just
> > > > managed to figure this out and it looks like it is definitely working
> > > > now, however, I need your assistance and ask you and other experts
> > > > what I can do in the below situation.
>
> > > > Now that I have the insert working, how can I say the following. Upon
> > > > insert if the FirstName, LastName and Email address are the same do
> > > > not insert into the Users table but insert into the other 2 tables all
> > > > the info. plus the appropriate user id.
>
> > > What you are asking is unclear but look at the Merge Statement if the
> > > choice in a single table is insert/update. Look at the INSERT FIRST,
> > > INSERT ALL, and INSERT WHEN statements.
>
> > > Also consider doing a SELECT COUNT(*) to determine if the record has
> > > already been inserted.
>
> > > You can find all of these in Morgan's Library atwww.psoug.org.
> > > Look up MERGE and INSERT.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damor..._at_x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
>
> > > - Show quoted text -
>
> > Sorry DA Morgan let me clarify. I'm not sure how much you know about
> > front end coding but I will keep it simple. I have an ASP page that
> > when the user clicks on the page he is asked a serieds of question.
> > Like what is your first name, what is your last name, what is your
> > email adress, what kind of equipment do you want, for what purpose
> > etc. After the user fills in all this information he/she will click a
> > submit button. When they click the submit button, behind the scenes I
> > do an insert into(based on the insert statement that I showed you
> > above) into the users table, hardware table and approver table. Now
> > if Joe Smith fills out this form and then clicks submit and then comes
> > to the page again, fills out the form again and clicks submit, well I
> > would not want to insert his name twice into the users table but
> > rather have unique users being captured in this table. So therefore,
> > how do I code this in the backend to say if the user is not unique
> > don't insert the entry into the users table but all the other info.
> > that he/she submitted will be inserted into the respective tables. I
> > hope this is clear. Please let me know if it is not and in the mean
> > time I will have a look at what you mentioned
>
> Such is the folly of ID columns as the primary key.
>
> Change your data model!- Hide quoted text -
>
> - Show quoted text -

Sorry guys I am still a bit confused. I was hoping for my primary key to be the next value in oracle. This is an auto incremented number. DA morgan what would the select count(*) do for me and where would I include this(before the insert). Sorry I am just a bit confused as I have never done this before.

Ed Prochak could you give me a sample idea of the data model. I have no problem in changin it as I want to build this application right. Sorry just confused on the answers. One is saying to change the data model and the other to add a select count. Received on Tue May 01 2007 - 03:42:56 CDT