Update table from another with linked fields

[benjamenus]

I'm sure this is really simply. I want to perform the following update but this will not work as I don't mention the table tempmjb1. However, if I type 'update policy, tempmjb1' I get a 'missing SET keyword' error. What's the Oracle solution please?

update
policy
set
policy.cov_cod = tempmjb1.new_cod,
policy.des_txt = tempmjb1.new_des,
policy.chg_by = tempmjb1.chg_by,
policy.chg_dte = tempmjb1.chg_dte
where
policy.acc_num = tempmjb1.acc_num
and policy.cov_maj = tempmjb1.cov_maj
and policy.cov_cod = tempmjb1.cov_cod
and policy.loc_cod = tempmjb1.loc_cod;

 

[VSU]

[tt]
-- Variant #1
UPDATE policy
SET (policy.cov_cod, policy.des_txt, policy.chg_by, policy.chg_dte)
= (SELECT new_cod, new_des, chg_by, chg_dte
FROM tempmjb1
WHERE policy.acc_num = tempmjb1.acc_num
AND policy.cov_maj = tempmjb1.cov_maj
AND policy.cov_cod = tempmjb1.cov_cod
AND policy.loc_cod = tempmjb1.loc_cod)
WHERE EXISTS
(SELECT * FROM tempmjb1
WHERE policy.acc_num = tempmjb1.acc_num
AND policy.cov_maj = tempmjb1.cov_maj
AND policy.cov_cod = tempmjb1.cov_cod
AND policy.loc_cod = tempmjb1.loc_cod)
;


-- Variant #2
-- Will work only when POLICY primary key
-- is also a primary key of IN-LINE VIEW (SELECT ... )
-- (Key-preserved view)
UPDATE
(SELECT policy.cov_cod, tempmjb1.new_cod
, policy.des_txt, tempmjb1.new_des
, policy.chg_by, tempmjb1.chg_by AS t_chg_by
, policy.chg_dte, tempmjb1.chg_dte AS t_chg_dte
WHERE policy.acc_num = tempmjb1.acc_num
AND policy.cov_maj = tempmjb1.cov_maj
AND policy.cov_cod = tempmjb1.cov_cod
AND policy.loc_cod = tempmjb1.loc_cod)
SET
cov_cod = new_cod
, des_txt = new_des
, chg_by = t_chg_by
, chg_dte = t_chg_dte
;
[/tt]

 

[benjamenus]

Cheers VSU