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 [email protected] http://lists.lugod.org/mailman/listinfo/vox-tech
