• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 877
  • Last Modified:

Oracle update query

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
cmrecruitment
Asked:
cmrecruitment
  • 4
2 Solutions
 
schwertnerCommented:
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
 
actonwangCommented:
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
Ultimate Tool Kit for Technology Solution Provider

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 now.

 
awking00Commented:
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
 
actonwangCommented:
>>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
 
actonwangCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

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 now.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now