Ask Experts Questions for FREE Help !
    hmmduh's Avatar
    hmmduh Posts: 1, Reputation: 1
    New Member

    Feb 26, 2010, 05:16 PM
    How to refer another table in mysql within a LIKE statement
    I have two tables and I want to delete rows in one table1 based on all the data in field2 of another table2 found in field1 of table1.

    I believe the solution to be like this

    DELETE FROM table1 WHERE field1 LIKE '%'+ table2.field2 +'%'

    This is not working... WHY? Is there another option.

    I want to delete rows of a table based on my keywords in table2
    jmd814's Avatar
    jmd814 Posts: 10, Reputation: 2
    New Member

    Mar 1, 2010, 01:24 PM

    My first instinct is to have you try a DELETE... WHERE IN (SELECT [from second table])
    But on the MySQL documentation there was a comment that it wasn't working and he came up with his own solution.
    Instead of reposting, go here to see what he did:

    MySQL :: MySQL 5.0 Reference Manual :: 12.2.2 DELETE Syntax

    Search for "WHERE IN"
    slapshot_oi's Avatar
    slapshot_oi Posts: 1,537, Reputation: 589
    Ultra Member

    Mar 18, 2010, 08:11 AM
    Quote Originally Posted by hmmduh View Post
    DELETE FROM table1 WHERE field1 LIKE '%'+ table2.field2 +'%'

    This is not working...WHY? Is there another option.
    A simple dot-reference won't be recognized by the SQL parser. You need a sub-query:
    WHERE field1 LIKE '%' + (
    SELECT field2
    FROM b
    WHERE field2 = 'some filter value here'
    ) + '%'
    This code does the same thing as jm814's method, except his where clause sub-query returns an entire result set, mine just returns one value. I code like this because it's so verbose it's self-documenting.

Not your question? Ask your question View similar questions


Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.

Check out some similar questions!

What does sheathing refer to [ 1 Answers ]

On the nate exam, the question is "in what trade, does the term 'sheathing' refer to?

Refer to drawer cheque [ 1 Answers ]

Please me in recoring the following refer to drawer writes a cheque for r 2090.51 and is returned to drawer for insufficient funds in account. I have already entered this cheque in cash payments journal. Please assist in showing how to record the appropriate corrections. My books...

VA Refer/Eligible [ 3 Answers ]

We have the income just not the credit scores. How can we get this to go to approved?

View more questions Search