?
Solved

Oracle update query

Posted on 2006-05-03
8
Medium Priority
?
873 Views
Last Modified: 2012-06-27
Hi,

we are trying to create an oracle query that updates a table using a View. Managed to create a working version in MS SQL but can not replicate query in oracle. Believe it might be how we are referencing the View. Below is a copy of the code I am currectly using.

"Ezlive" is the database.
"MNME" is the table I am trying to update.
"S_HOSP_SKILL" and "S_VALID_FORIEGN_CAND" are the views.

Code:

UPDATE (ezlive.MNME LEFT JOIN S_VALID_FORIEGN_CAND ON ezlive.MNME.NM_IDNO =
S_VALID_FORIEGN_CAND.NM_IDNO) LEFT JOIN S_HOSP_SKILL ON ezlive.MNME.NM_IDNO =
S_HOSP SKILL.NM_IDNO SET ezlive.MNME.NM_TYPE = 'D'
WHERE ((S_VALID_FORIEGN_CAND.NM_IDNO Is Null) AND (S_HOSP_SKILL.NM_IDNO Is Null));

This query should only update records in the mnme table if they are not inlcude in either of the views.

Just summaries, I need to now whether you can reference View when doing an update query in oracle. If it is possible, I also need to know how to do it.

Any help on this would be very help.

thanks david jacques
0
Comment
Question by:cmrecruitment
  • 4
6 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 1000 total points
ID: 16594696
If you want to INSERT, UPDATE, DELETE records based on views you have two ways. The FIRST one is to provide DB triggers of type "Instead of" to do these actions.

Example:
create or replace view CAND_PGRS_VIEW
as select
cp.id, cp.to_employee, cp.progress_type, cp.planned_on_date,
cp.held_on_date, cp.result, cp.remark,
cpt.name, cpt.place, cpt.in_use
from CANDIDATE_PROGRESS cp, CANDIDATE_PROGRESS_TYPE cpt
where cp.progress_type = cpt.id;


CREATE OR REPLACE TRIGGER candidate_progress_update
INSTEAD OF UPDATE  ON cand_pgrs_view
DECLARE
  duplicate_info EXCEPTION;
  PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
  UPDATE staff.candidate_progress
   SET  planned_on_date = :new.planned_on_date,
       held_on_date    = :new.held_on_date,
       result          = :new.result,
       remark          = :new.remark
  WHERE id = :new.id;
END candidate_progress_update;
/


CREATE OR REPLACE TRIGGER candidate_progress_input
INSTEAD OF INSERT  ON cand_pgrs_view
DECLARE
  duplicate_info EXCEPTION;
  PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
  INSERT INTO staff.candidate_progress
           (id, to_employee, progress_type, planned_on_date,
           held_on_date, result, remark )
  VALUES  (:new.id, :new.to_employee, :new.progress_type, :new.planned_on_date,
           :new.held_on_date, :new.result, :new.remark );
EXCEPTION
  WHEN duplicate_info THEN
  RAISE_APPLICATION_ERROR (
   num => -21107,
   msg => 'Duplicated Candidate Progress Record');
END candidate_progress_input;
/

0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 1000 total points
ID: 16596117
hi, cmrecruitment,

        in Oracle, yes , you can update on columns in views or subqueries. But you only can modify columns from key-preserved table.

Acton
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 32

Expert Comment

by:awking00
ID: 16596575
UPDATE ezlive.MNME
SET ezlive.MNME.NM_TYPE = 'D'
WHERE ezlive.MNME.NM_IDNO NOT IN
(SELECT NM_IDNO FROM S_VALID_FORIEGN_CAND
 UNION
 SELECT NM_IDNO FROM S_HOSP_SKILL)

0
 
LVL 19

Expert Comment

by:actonwang
ID: 16596784
>>UPDATE (ezlive.MNME LEFT JOIN S_VALID_FORIEGN_CAND ON ezlive.MNME.NM_IDNO =
S_VALID_FORIEGN_CAND.NM_IDNO) LEFT JOIN S_HOSP_SKILL ON ezlive.MNME.NM_IDNO =
S_HOSP SKILL.NM_IDNO SET ezlive.MNME.NM_TYPE = 'D'
WHERE ((S_VALID_FORIEGN_CAND.NM_IDNO Is Null) AND (S_HOSP_SKILL.NM_IDNO Is Null));

you need to try as:

UPDATE
(select ezlive.MNME.NM_TYPE, S_VALID_FORIEGN_CAND.NM_IDNO,S_HOSP_SKILL.NM_IDNO
 from  ezlive.MNME
 LEFT JOIN S_VALID_FORIEGN_CAND ON ezlive.MNME.NM_IDNO =S_VALID_FORIEGN_CAND.NM_IDNO
 LEFT JOIN S_HOSP_SKILL ON ezlive.MNME.NM_IDNO =S_HOSP SKILL.NM_IDNO) t
 SET t.NM_TYPE = 'D'
WHERE t.NM_IDNO Is Null AND t.NM_IDNO Is Null;
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16596826
this should work in any event:

UPDATE ezlive.MNME t
SET t.NM_TYPE = 'D"
WHERE not exists ( select 1 from  S_VALID_FORIEGN_CAND s1 where s1.NM_IDNO = t.NM_IDNO) and
           not exists (select 1 from S_HOSP_SKILL s2 where s2.NM_IDNO = t.NM_IDNO)
/
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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
Suggested Courses

850 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