0

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?

Newmania
  • 644
  • 1
  • 12
  • 17
  • And update with join, if u add a fiddle, we can help you better, but with this approach, now you can try. – Juan Ruiz de Castilla Oct 01 '15 at 15:51
  • Thanks I achieved it with a join: UPDATE (counsellors_professional_body_link AS a) INNER JOIN (counsellors_professional_body_link AS b) ON (a.professional_body_link_id=b.professional_body_link_id AND a.professional_bodyid=5 AND CHAR_LENGTH(a.membership_number) = 5) SET a.membership_number = CONCAT('0', b.membership_number) – Newmania Oct 02 '15 at 12:12

0 Answers0