Solved

Update set of records in Oracle

Posted on 2014-03-03
1
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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