On Thu, Nov 09, 2006 at 12:49:25PM -0800, Richard S. Crawford wrote: > I have a number of rows in my database which look something like this: > > > id | username | canonicalname | password > ---+----------+---------------+----------------------- > 01 | asmith | ART SMITH | md5 encrypted password > 02 | asmith1 | ART SMITH | <NULL> > 03 | bjones | BILL JONES | md5 encrypted password > 04 | bjones12 | BILL JONES | <NULL> > ---+----------+---------------+----------------------- > > > There are something like 275 entries like these. I'm having a hell of a time > finding a way to select rows like 02 and 04, where canonicalname is a > duplicate of the same value in another row and the password is blank, and > then deleting those rows. After executing my query, the table should look > like this: > > > id | username | canonicalname | password > ---+----------+---------------+----------------------- > 01 | asmith | ART SMITH | md5 encrypted password > 03 | bjones | BILL JONES | md5 encrypted password > ---+----------+---------------+----------------------- > > > Is there a simple way to execute a single select query which would pull up > all > the rows I need?
Not in MySQL. It takes 2 (well technically 3) SQL statements; create temporary table todelete select a.id as id from THETABLE a, THETABLE b where a.username=b.username AND a.canonicalname=b.canoncialname and a.password is null and b.password is not null; delete from THETABLE using todelete, THETABLE where todelete.id=THETABLE.id; drop temporary table todelete; Maybe other DB's are more intelligent about self-joins in deletes, in which case you can probably figure out how to combine all 3 of these based on what I've posted. --Ken -- Ken Bloom. PhD candidate. Linguistic Cognition Laboratory. Department of Computer Science. Illinois Institute of Technology. http://www.iit.edu/~kbloom1/
signature.asc
Description: Digital signature
_______________________________________________ vox-tech mailing list vox-tech@lists.lugod.org http://lists.lugod.org/mailman/listinfo/vox-tech