I have been tasked to find out why this code is not working. Previous employer set it up and it's never worked. What am I missing? The code is designed to remove extraneous data from a table.
delete from risktype
where risk_type_id not in
(select distinct(category_id) from risk where category_id is not null)
and risk_type_id not in
(select distinct (r1.risk_type_id) from risktype r1 left join risktype r2
on r1.risk_type_id = r2.risk_type_id
start with r1.risk_type_id in
(select risk_type_id from risktype where risk_type_id in (select distinct(category_id) from risk))
connect by prior r1.parent_risk_type_id = r2.risk_type_id)
order by risk_type_id;
This is the error message I received::
Absolute novice here, so any help would be greatly appreciated. Apologies for any errors in formatting, not many options available when on a phone.
Also should have mentioned, this is a scheduled script run on an Oracle database. I am using SQL Server to access this particular job through the Scheduler folder.