PL/SQL Update statement question

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?



xhatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dom_CottonCommented:
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
xhatAuthor Commented:
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
Dom_CottonCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pratikroyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pratikroyCommented:
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
xhatAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.