Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 3EF809FB2F8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 10 Nov 2006 12:08:00 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 79842-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 10 Nov 2006 16:07:46 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.225])
	by postgresql.org (Postfix) with ESMTP id F35C29FA3A7
	for <pgsql-sql@postgresql.org>; Fri, 10 Nov 2006 12:07:45 -0400 (AST)
Received: by wx-out-0506.google.com with SMTP id i28so510648wxd
	for <pgsql-sql@postgresql.org>; Fri, 10 Nov 2006 08:07:45 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=NA4LeGQJtV0jwnhlFul8IVfU151Yr+E5P3O/FVa9jlxy5ac5lpDGDlHM9DtnyHlTyYwLuvxhM2nQ7PpqYexTl8ohzsyn/8/MNBnmdNmC8QhnnwWwtLM2GXiqClJmzHBGIdctttgbx9pe53hiJ34kkFCwfvBkFBhlwqGigKesvAA=
Received: by 10.70.67.2 with SMTP id p2mr3307133wxa.1163174864791;
	Fri, 10 Nov 2006 08:07:44 -0800 (PST)
Received: by 10.70.72.14 with HTTP; Fri, 10 Nov 2006 08:07:44 -0800 (PST)
Message-ID: <cf9b4f3e0611100807wc1acc57xdd65944e3cd54bda@mail.gmail.com>
Date: Fri, 10 Nov 2006 11:07:44 -0500
From: "Travis Whitton" <tinymountain@gmail.com>
To: "Erik Jones" <erik@myemma.com>
Subject: Re: Wildcard LIKE and Sub-select
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4554A246.3010701@myemma.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_47108_11750240.1163174864573"
References: <cf9b4f3e0611100723r617301cen7f56902e288f1011@mail.gmail.com>
	<4554A246.3010701@myemma.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/114
X-Sequence-Number: 26766

------=_Part_47108_11750240.1163174864573
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I took off the USING clause like so, and it worked like a charm!

DELETE FROM keywords
WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'
                                                  FROM badwords)

Thanks so much,
Travis

On 11/10/06, Erik Jones <erik@myemma.com> wrote:
>
> Travis Whitton wrote:
> > Hi everybody,
> > I have two tables of the following structure:
> >
> > Table "keywords"
> >
> > column   | type
> > ---------------------
> > id           | integer
> > keyword | varchar(255)
> >
> > and
> >
> > Table "badwords"
> >
> > column   | type
> > ----------------------
> > badword  | varchar(255)
> >
> >
> > I need to delete all the rows from the keywords table where badword
> > partially matches the keyword field. I know I can do an exact match
> > with a sub-select, but I'm not sure how to structure a wildcard match
> > / like clause with a sub-select. Also, is that the best way to do it,
> > or should I be looking into full-text? I have roughly 10 million
> > keywords and 1 million badwords.
> >
> > Thanks,
> > Travis
> >
> Hmm...  Maybe (this is untested):
>
> DELETE FROM keywords
> USING badwords
> WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'
>                                                    FROM badwords)
>
> --
> erik jones <erik@myemma.com>
> software development
> emma(r)
>
>

------=_Part_47108_11750240.1163174864573
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I took off the USING clause like so, and it worked like a charm!<br><br>DELETE FROM keywords<br>WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM badwords)<br>

<br>Thanks so much,<br>Travis<br><span></span><br><div><span class="gmail_quote">On 11/10/06, <b class="gmail_sendername">Erik Jones</b> &lt;<a href="mailto:erik@myemma.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
erik@myemma.com</a>&gt; wrote:</span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Travis Whitton wrote:<br>&gt; Hi everybody,<br>&gt; I have two tables of the following structure:
<br>&gt;<br>&gt; Table &quot;keywords&quot;<br>&gt;<br>&gt; column&nbsp;&nbsp; | type<br>&gt; ---------------------<br>&gt; id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | integer<br>&gt; keyword | varchar(255)<br>&gt;<br>&gt; and<br>&gt;<br>&gt; Table &quot;badwords&quot;
<br>&gt;<br>&gt; column&nbsp;&nbsp; | type<br>&gt; ----------------------<br>&gt; badword&nbsp;&nbsp;| varchar(255)<br>&gt;<br>&gt;<br>&gt; I need to delete all the rows from the keywords table where badword<br>&gt; partially matches the keyword field. I know I can do an exact match
<br>&gt; with a sub-select, but I'm not sure how to structure a wildcard match<br>&gt; / like clause with a sub-select. Also, is that the best way to do it,<br>&gt; or should I be looking into full-text? I have roughly 10 million
<br>&gt; keywords and 1 million badwords.<br>&gt;<br>&gt; Thanks,<br>&gt; Travis<br>&gt;<br>Hmm...&nbsp;&nbsp;Maybe (this is untested):<br><br>DELETE FROM keywords<br>USING badwords<br>WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM badwords)<br><br>--<br>erik jones &lt;<a href="mailto:erik@myemma.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">erik@myemma.com</a>
&gt;<br>software development<br>emma(r)<br><br></blockquote></div><br>


------=_Part_47108_11750240.1163174864573--