Check value existance while performing a UPDATE query

Question

I need to perform a simple update query where the update should only be done, if there is no value with updating value (item_name). For example,

DB table [item_types]

item_id(PK) | item_name

Assuming there exist item_id with 6, My attempt is

UPDATE item_types as k SET k.item_name = 'item_1' WHERE NOT EXISTS (SELECT * FROM item_types as a WHERE a.item_name = 'item_1') AND k.item_id = '6'

But this gives me error

"You can't specify target table 'k' for update in FROM clause"

Could you please explain the error here and how can I achieve this ?

Thank you

Answer 1

You could put a unique restraint on the item_name field and catch the error.