[AccessD] SQL DEVELOPER question - running a delete query in a loop

Benson, William (GE Global Research, consultant) Benson at ge.com
Wed Sep 12 09:53:14 CDT 2012


Hi all, I recently recognized that there was no unique key on a table (sid_user_sites) and duplicates were getting inserted.

------------------------ WHAT I AM AFTER ----------------------------
Is there a means in SQL DEVELOPER to set up a delete query to iterate in a loop until no more records are deleted

------------------------ BACKGROUND ----------------------------

I wrote a poorly designed query which had to be run numerous times. I will show both it, and the improved query, which only had to be run once below. Bear in mind this is just background, I am trying to avoid people writing me telling me how much better the query could have been written - I KNOW THAT AND THAT IS WHY I WROTE IT BETTER. My real, and ONLY question here, is can a query be run in a loop in SQL DEVELOPER. Thanks!!

-------------------- ORIGINAL QUERY-------------------------
--Has to be run until no more records can be deleted-----
delete from sid_user_sites where user_site_id in
(select max(user_site_id)
from sid_user_sites
where
user_sso || site_duns_no in 
(
Select user_SSO || Site_Duns_No 
from sid_user_sites 
group by user_SSO || Site_Duns_No
having count(user_site_ID) >1
)
group by user_sso, site_duns_no
)

----BETTER QUERY -----------

delete from sid_user_sites where user_site_id not in
(
select MinID
from
(
Select user_sso, site_duns_no, min(user_site_id) MinID
from sid_user_sites
group by user_sso, site_duns_no
)
)

Now, that can probably be made even simpler with a join - at least I think it could in Access... though maybe not Oracle.



More information about the AccessD mailing list