[vox-tech] A complicated SQL question
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? -- Richard S. Crawford (http://www.mossroot.com) Editor In Chief at Daikaijuzine (http://www.daikaijuzine.com) Check out the Cthulhu Wiki: http://www.mossroot.com/cthulhuwiki AIM: Buffalo2K / GTalk: [EMAIL PROTECTED] Skype/Gizmo: underpope You can't trust your judgement when your imagination is out of focus. (Mark Twain) ___ vox-tech mailing list vox-tech@lists.lugod.org http://lists.lugod.org/mailman/listinfo/vox-tech
Re: [vox-tech] A complicated SQL question
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
Re: [vox-tech] A complicated SQL question
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? Joining the table to itself should work. select pw.*, pw2.cname from pw inner join pw as pw2 on pw.cname=pw2.cname where pw.password is null and pw2.password is not null group by pw.cname; -t ___ vox-tech mailing list vox-tech@lists.lugod.org http://lists.lugod.org/mailman/listinfo/vox-tech
