I'm trying to update a field in a table used in a sub query:
UPDATE counsellors_professional_body_link SET membership_number = CONCAT('0', membership_number) WHERE professional_body_link_id IN (SELECT professional_body_link_id FROM counsellors_professional_body_link WHERE professional_bodyid=5 AND CHAR_LENGTH(membership_number) = 5)
But I am told:
You can't specify target table 'counsellors_professional_body_link' for update in FROM clause
How best to do this please?