Update Table From Same Table

I am using MySQL 5.0.51a and phpMyAdmin 2.11.4. I need to update a field in a table. I have a list of products in a table in which several hundred need to be renamed. The products that need to be renamed all end with -HOLD. I need to remove -HOLD from the products name. This is the error I get '#1093 - You can't specify target table 'prodlist' for update in FROM clause'. This is my sql:

UPDATE `prodlist` SET `prodname` = LEFT(`prodname`,LENGTH(`prodname`)-5) WHERE `prodname` IN(SELECT `prodname` AS product FROM `prodlist` WHERE RIGHT(`prodname`,5) = '-HOLD')

Could someone please tell me what I am doing wrong or how I can accomplish this? Thank you very much.

I don't know MySQL, but your problem seems to be straight SQL

In plalin SQL, the query would be:

UPDATE `prodlist` SET `prodname` = LEFT(`prodname`,LENGTH(`prodname`)-5)
WHERE RIGHT(`prodname`,5) = '-HOLD')

Thank you very much dykebert. That works very nicely. I guess I was trying to make it too complicated.