On update mysql row (You can't specify target table 'x' for update in FROM clause)

Question

I am trying to update MySQL row.

my query is

update x set available_material_id = null where id not in (select id from x where additional_info = 1);

and I am getting this error message: You can't specify target table 'x' for update in FROM clause

can anybody help me with this issue?

I am using MySQL version 5.6.38.

i saw this You can't specify target table for update in FROM clause answer but i could not figure this out.

Answer 1

Use a left join:

update x left join
       x xx
       on x.id = xx.id and xx.additional_info = 1
    set available_material_id = null
    where xx.id is null;