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

Benson, William (GE Global Research, consultant) Benson at ge.com
Wed Sep 12 11:06:32 CDT 2012


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





More information about the AccessD mailing list