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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 30 Apr 2007 11:30:56 -0700
Message-ID: <1177957854.434076@bubbleator.drizzle.com>

MrHelpMe 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-Hide quoted text -
>>>> - 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- Hide quoted 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.

You can not do it unless you have a way of defining a unique person. There are, for example, two Daniel Morgans teaching at the University of Washington: At least 12 of us in the Seattle phone book.

What is it you are going to use to define a unique person?

Once you know that it becomes your primary key and then a simple SELECT COUNT(*)
INTO i
FROM ....; answers the question.

IF i = 0 THEN

   ... new customer
ELSIF i = 1 THEN

   ... existing customer
ELSE
   ... raise an exception
END IF;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Apr 30 2007 - 13:30:56 CDT