?
Solved

Update set of records in Oracle

Posted on 2014-03-03
1
Medium Priority
?
446 Views
Last Modified: 2014-03-10
I need to update a set of records in Oracle but to do so I need to join two tables to get the exact records that I need to update.  I am trying this statement but getting a non-preserved key error.

I have attached the update statement (note the tables and field names are changed to keep information confidential)update-records.txt
0
Comment
Question by:gilnari
1 Comment
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39902284
UPDATE employee ep
   SET reported_name = 'XYZ'
 WHERE ep.state LIKE 'ACME'
   AND table_name LIKE 'EMPLOYEE'
   AND ep.county LIKE 'ACME'
   AND EXISTS
           (SELECT NULL
              FROM versions v
             WHERE ep.name = v.name AND ep.version = v.version);


"table_name" wasn't aliased so I don't know if it was from employee or versions.
Simply move that condition to the EXISTS subquery if it applies to versions.

Also,  your "LIKE" conditions don't have wildcards.  So those should probably be equalities instead.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

840 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