Oracle update query
Posted on 2006-05-03
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.
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