Let's say I have a MYSQL table with the following data in one column:


I want to find (and delete) rows that have "sub" as the prefix IF AND ONLY IF another row exists with the root word. So in this example I want to delete "submarine", "subway", and "submission" but NOT "subjugate" (since "jugate" doesn't exist).

How can I do this?

UPDATE: Okay, thank you @Akina. I am getting a:

#1054 - Unknown column 't1.word' in 'where clause'

when I try. This is my version:

FROM `en` AS t1, `en` AS t2 
WHERE LEFT(t1.word, 3) = 'sub'
AND t1.word = CONCAT('sub', t2.word)

What am I doing wrong?

  • 1
    Hmmm... interesting philosophical debates possible here. The words mission and submission have different meanings - to submit to someone else might mean (or not) subverting one's own mission? Seagulls are marine creatures but are not submarine. Have you looked at any of the academic work in this area regarding NLP and stemming/derivation? Also, your SQL version could be very important. p.s. welcome to the forum! :-)
    – Vérace
    Nov 20, 2019 at 14:17
  • Again ... you should know how the column containing those words is named ... my crystal bowl doesn't light up nor shows what you see ...
    – eagle275
    Nov 20, 2019 at 15:20

1 Answer 1

FROM table t1, table t2
WHERE LEFT(t1.word, 3) = 'sub'
  AND t1.word = CONCAT('sub', t2.word)
  • See my comment - I think that the OP is looking for a (much) more general solution?
    – Vérace
    Nov 20, 2019 at 14:13
  • @Vérace I have seen... and I think that the values are synthetic.
    – Akina
    Nov 20, 2019 at 14:15
  • @Akina This is probably a dumb question, but I tried that and am getting: #1054 - Unknown column 't1.word' in 'where clause'
    – Kelly
    Nov 20, 2019 at 14:29
  • you need to enclose reserved words in backticks in mysql-server .. so table - or replace it with the actual name of the table that you want to deal with
    – eagle275
    Nov 20, 2019 at 14:34
  • 1
    better edit your original post with that .. as comments are limited ^^
    – eagle275
    Nov 20, 2019 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.