I need to update the rows on (D.SELLER_ACCOUNT_ID = S.ACCOUNT_ID AND D.CATEGORY_ID = S.CATEGORY_ID_OLD)
.
How is it possible to correct the query below in Oracle?
MERGE INTO T_EVM_CLASSIFICATION D
USING (SELECT CATEGORY_ID_NEW, CATEGORY_ID_OLD, ACCOUNT_ID FROM DATA_TEMP) S
ON (D.SELLER_ACCOUNT_ID = S.ACCOUNT_ID AND D.CATEGORY_ID = S.CATEGORY_ID_OLD)
WHEN MATCHED THEN UPDATE SET D.CATEGORY_ID = S.CATEGORY_ID_NEW;
If there is 1:1 relationship use a simple update
:
update t_evm_classification d set category_id = (
select category_id_new
from data_temp s
where d.seller_account_id = s.account_id
and d.category_id = s.category_id_old )
The above update will nullify category_id
if data in temp table is not present. To avoid this add where
clause:
update t_evm_classification d
set category_id = ( select category_id_new
from data_temp s
where d.seller_account_id = s.account_id
and d.category_id = s.category_id_old )
where (d.seller_account_id, d.category_id) in
(select s.account_id, category_id_old from data_temp s)