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

Randy's fine, just don't call me late for supper...
1 - Creates an alias, sort of like a temp table.
2 - No.

Haven't used SQL Developer, but I'm assuming you can do a table backup?  As for finding the dupes you can use this and write them to a temp table for safe keeping before you run the delete.
select  field1, field*
from tablename
group by  fieldthasdupevalue
having count(*) > 1

-----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
-------------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.