SQLTeam.com | Weblogs | Forums

Script to remove extraneous data from a table produces error


#1

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.

"begin
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;
commit;
end;"

This is the error message I received::

"ORA-06550: line 10, column 60:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
"

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.


#2

This is a Microsoft SQL Server forum not Oracle. At first glance, I don't know why you would need the order by clause at the end. Also, it looks like it wants to delete all records that are not in the category table by doing the first "not in" then, as a double check, it wants to exclude all the records that are in the Category table. Try something like this, but change the delete to a select to test what would be deleted.

DELETE rt
FROM     risktype rt
	left join risk r
		on rt.risk_type_id = r.category_id
where r.category_id is null

#3

Hi Mike, I realized that afterwards. I totally missed the part that this was purely Microsoft. For that I apologise to all. Thank you for your suggestion, and I continued to work on this. The issue with the script is the "order by risk_type_id;" line at the end. I was removing items to test what was happening and it worked after removing this line.

Thank you for you help. Liked the response.