Hi I have following tables.
Onpremise table and cloud table with columns employee id and menuid for assigning menu rights.
with op_menurights as
(Select 1 employee_id, 100 menu from dual union all
Select 2 employee_id, 110 menu from dual union all
Select 2 employee_id, 200 menu from dual union all
Select 4 employee_id, 107 menu from dual
),
cloud_menurights as
(Select 1 employee_id, 100 menu from dual union all
Select 1 employee_id, 101 menu from dual union all
Select 2 employee_id, 110 menu from dual union all
Select 2 employee_id, 200 menu from dual union all
Select 3 employee_id, 107 menu from dual union all
Select 4 employee_id, 107 menu from dual
)
I want to remove all entries from cloud_menurights table that are no longer in on premise table op_menurights. Is it possible in a single query?
In this case the following records to be removed from cloud table
Employee id Menu id
1 101
3. 107
Please help
Thank you