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

Thinking that maybe Oracle doesn't like a subquery, I tried this instead:

DROP TABLE BILL_TEMP;
CREATE TABLE BILL_TEMP AS
(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)
)
);

--TEMP TABLE CREATED OK.... BUT NEXT QUERY SPINS AND SPINS...
delete
from sid_user_sites S
where not exists
(Select MinID 
from BILL_TEMP 
Where MinID = S.user_site_id);


STILL DOESN'T WORK.... How can I get rid of these stinking records using this approach or similar?

-----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 12:07 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL DEVELOPER question - running a delete query in a loop

Randall (or Anthony?)

I do not have a development database to try it on. 

1) deleting bob ... from TableName as bob.  Hmmm... That doesn't make sense to me, I can't risk hurting the table until I can understand the SQL, can you explain it?
2) Can I rollback if I don't like the result

Also, now I have a different problem that I did not think I had ... and in fact, I thought his query ran the first time I tried it and NOW  it doesn't seem to work:

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
)
);

It just spins off into space and I have to kill the task.

Anything wrong with it?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Wednesday, September 12, 2012 11:05 AM
To: Access Developers discussion and problem solving
Subject: Re: [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



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