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)