asked on

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

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Acton Wang

here is a good info on this:

http://asktom.oracle.com/pls/ask/f?p=4950:8:9301066354755546653::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:548422757486

awking00

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)

Acton Wang

>>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;

Acton Wang

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)
/