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

Try this...
SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE bob
FROM tablename as bob
INNER JOIN
(SELECT field1, field2
FROM tablename
GROUP BY  field1, field2 HAVING count(*) > 1)
AS c ON c.field1 = bob.field1
AND c.field2 = bob.field2 
SET ROWCOUNT 0

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Benson, William (GE Global Research, consultant)
Sent: Wednesday, September 12, 2012 10:53 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] SQL DEVELOPER question - running a delete query in a loop

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.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-------------Disclaimer---------------

This electronic message and its contents and attachments contain information from Sentara Healthcare and is confidential or otherwise protected from disclosure. The information is intended to be for the addressee only.

If you are not the addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this electronic message in error, please notify us immediately and destroy the original message and all copies.