From: Travis Whitton <tinymountain@gmail.com>
To: Erik Jones <erik@myemma.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Wildcard LIKE and Sub-select
Date: Fri, 10 Nov 2006 11:07:44 -0500
Message-ID: <cf9b4f3e0611100807wc1acc57xdd65944e3cd54bda@mail.gmail.com> (raw)
In-Reply-To: <4554A246.3010701@myemma.com>
References: <cf9b4f3e0611100723r617301cen7f56902e288f1011@mail.gmail.com>
<4554A246.3010701@myemma.com>
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)
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tinymountain@gmail.com, erik@myemma.com
Subject: Re: Wildcard LIKE and Sub-select
In-Reply-To: <cf9b4f3e0611100807wc1acc57xdd65944e3cd54bda@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox