Check value existance while performing a UPDATE query

Posted by nimo on Stack Overflow See other posts from Stack Overflow or by nimo
Published on 2011-01-16T15:46:25Z Indexed on 2011/01/16 15:53 UTC
Read the original article Hit count: 106

Filed under:

Hi,

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

© Stack Overflow or respective owner

Related posts about mysql