Let's say I have a MYSQL table with the following data in one column:
marine submarine way subway subjugate submission mission
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:
SELECT t1.* 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?