Solved

PL/SQL Update statement question

Posted on 2004-10-21
2,177 Views
Last Modified: 2008-02-26
I'm trying to update an Oracle 9i table with data that exists in a separate table.  TMFR_PERS_RMMT lists the primary tenant and any roommate requests.  For each roommate request (max 3), the primary and one roommate request record is written.  Example:  I'm the primary.  I request Britney Spears, Carmen Electra and Jenna Elfman as my roommates.  Thus, my first record lists my id and Britney's id.  The second record lists my id and Carmen's id, etc.  Part of each record is a first and last name.  When the table was first populated, the first and last name of the primary was inserted.  It should have been the roommates name data instead.  So, I'm trying to update the name data with data from the PERS table, which contains the first and last name of all registered students.  Only students already in the PERS table are allowed in the TMFR_PERS_RMMT table, so there's no problem with ids being in the TMFR_PER_RMMT table that aren't in the PERS table.  

Here is my update statement:

update tmfr_pers_rmmt set (val_nam_first, val_nam_last) =
(select pers.val_nam_first, pers.val_nam_last from
pers where
tmfr_pers_rmmt.id_pers_rmmt = pers.id_pers)
where exists
(select pers.val_nam_first, pers.val_nam_last from
pers where
tmfr_pers_rmmt.id_pers_rmmt = pers.id_pers)
/

Here is the output from Oracle SQL*Plus console:

230 rows updated.

The problem is that the data isn't changing.  I've committed the transaction by logging out of TOAD and logging back in, but despite the SQL*Plus console's message, the data is not updating.  I've debugged the script by listing out the actual names, and the correct data is being returned.  So, like, why can't I get Britney, Carmen and Jenna into my table?



0
Question by:xhat
    6 Comments
     
    LVL 2

    Expert Comment

    by:Dom_Cotton
    Shouldn't that be an insert statement anyway? like...

    insert into tmfr_pers_rmmt select pers.val_nam_first, pers.val_nam_last from
    pers where tmfr_pers_rmmt.id_pers_rmmt = pers.id_pers

    So you insert the new values directly from the select?

    I am not sure why the query isn't executing.... is your toad set to rollback on exit and not commit?

    HTH, DJ
    0
     

    Author Comment

    by:xhat
    No, it's not an insert statement.  The records are already in.  They just have the wrong name data.

    As for rollback, no.  TOAD prompts for commit on exit, and I commit.  I've also commited in the SQL statement (not shown.)  Either way, though, it doesn't change my data.  

    I've even gone so far as to check the PERS table to make sure it's not the table being updated, but all is fine there.
    0
     
    LVL 2

    Expert Comment

    by:Dom_Cotton
    ok I am guessing a little now (and after reading the post again), is the "where exists" really needed?  I think you are just duplicating the selection... the first select statement will only return 1 row if it exists or 3 if they exist, etc...

    DJ
    0
     
    LVL 9

    Accepted Solution

    by:
    Let me tell you that Toad does not commit by default. You will have to Commit the transaction Explicitly. I believe your transaction was not committed, due to which you can't see the updated records. The UPDATE statement looks perfectly fine to me.

    I am sure that the COMMIT will give you the desired results.

    Just in case that does'nt help, I would be really intrigued about the problem. In this case, Could you pick up some sample IDs from the two tables, and post the results here ?

    Something like :

    Select val_nam_first, val_nam_last from tmfr_pers_rmmt where id_pers_rmmt in (1,2,3,4,5);

    select val_nam_first, val_nam_last from pers where id_pers in (1,2,3,4,5);

    change 1-5, with the IDs that you have in your table.

    I would be surprised if both of them don't return the same result
    0
     
    LVL 9

    Expert Comment

    by:pratikroy
    Just in case you did'nt know, you could click on 'Database' on the top menu, and then click on 'Commit' to commit the transaction. You could also click on the icon that is for 'Commit'ing transaction.

    You could also write the 'commit;' on the command window, and execute the command.
    0
     

    Author Comment

    by:xhat
    Pratikroy,

    You are correct about the commit statement.  I was exiting TOAD and explicitly committing my transactions.  The problem, however, was that I had to run the commit from the SQL*Plus console.  Once I went back and did that, it updated properly.  I *thought* I had a commit statement in the SQL script, but upon further examination I was dead wrong.  Simple errors lead to complex problems.

    Thanks for the quick reply.


    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now