Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PL/SQL Update statement question

Posted on 2004-10-21
6
Medium Priority
?
2,182 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
Comment
Question by:xhat
  • 2
  • 2
  • 2
6 Comments
 
LVL 2

Expert Comment

by:Dom_Cotton
ID: 12370836
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
ID: 12371191
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
ID: 12371350
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Accepted Solution

by:
pratikroy earned 500 total points
ID: 12371730
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
ID: 12371756
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
ID: 12372344
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

916 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