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?
mission
andsubmission
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! :-)