MySQL WHERE LIKE Statement : How to delete rows in a table where a column substring not equals a desired substring?

Question

I want to delete the kontext(column_name) values that have a different substrinct than the substrinct that as fmea value different than null. I can select those values trough this query:

select * 
FROM fmea001 
where substring_index(kontext, ".", 1) 
    not in  (Select substring_index(kontext, ".", 1) 
             from fmea001 
             where fmea is not null 
               and lkey = 9) 

However when I try to delete

delete * 
FROM fmea001 
where substring_index(context, ".", 1) 
    not in  (Select substring_index(context, ".", 1) 
             from fmea001 
             where fmea is not null 
               and lkey = 9)) 

I get this error

Error Code: 1093. You can't specify target table 'fmea001' for update in FROM clause

Do you know another way to do the delete statement? Thanks!

Answer 1

MySQL dodes not support reoponing the table being modified (either updated or deleted from) in a subquery.

If I follow you correctly, you could write this as an anti-left join:

delete f
from fmea001 f
left join fmea001 f9
    on  substring_index(f9.kontext, '.', 1) = substring_index(f.kontext, '.', 1) 
    and f9.fmea is not null
    and f9.lkey = 9
where f9.fmea is null