DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Date:From:Subject:To:MIME-Version:Content-Type;
	b=YRF8/607Qfz61YlHV5WJk7RHSHSWGS0dFKvGWZcbpTzWRTxZEukg9mslW7Nx14FKDE9Rr4ReSWMMgEtszV/HrPqe2mdSifO0KEMCofiwpscD5eutOKBq7PJCFR21vKMp/z3vGRiBZFFpzGH9VVPdalGn9vJpE7m74rQkWQinbTA=
	;
Message-ID: <20060218174158.74971.qmail@web50312.mail.yahoo.com>
Date: Sat, 18 Feb 2006 09:41:58 -0800 (PST)
From: <ogjunk-pgjedan@yahoo.com>
Subject: Need help: Find dirty rows, Update, Delete SQL
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hello,

I need a bit of help with some SQL.
I have two tables, call them Page and Bookmark.
Each row in Page can have many Bookmarks pointing to it, and
they are joined via a FK (Page.id = Bookmark.page_id).

Page has a 'url' column: Page.url, which has a unique index on it.

My Page.url column got a little dirty, and I need to clean it up,
and that's what I need help with.

Here is an example of dirtiness:

Page:

id=1 url = 'http://example.com/'
id=2 url = 'http://example.com/#'     -- dirty
id=3 url = 'http://example.com/#foo'  -- dirty

The last two rows are dirty.  Normally I normalize URLs before
inserting them, but these got in, and now I need to clean them.

The problem is that rows in Bookmark table may point to dirty
rows in Page, so I can't just remove the dirty rows, and I can't
just update 'url' column in Page to 'http://example.com/',
because that column is unique.

Is there some fancy SQL that I can use them to find the dirty
rows in page (... where url like '%#%') and then find rows in
Bookmark table that point to them, then point those rows to
good rows in Page (e.g. id=1 row above), and finally remove the
dirty rows from Page?

Any help would be greatly appreciated.

I'm using Pg 8.0.3

Thanks,
Otis