MYSQL Find (and Delete) Word Prefixes and Suffixes 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?
DELETE t1.*
FROM table t1, table t2
WHERE LEFT(t1.word, 3) = 'sub'
  AND t1.word = CONCAT('sub', t2.word)