Comments - Using UPDATE with a recursive query - not working (error #1241)

1 year, 4 months ago Neil Dempster

While I was told UPDATE is not supported, I did manage to get a recursive UPDATE to work. The solution to this problem is posted below. My mistake was too many columns in the final SELECT statement. This works:

UPDATE positions SET bu_div = 'Customer Services' WHERE jobholder_uid IN ( WITH RECURSIVE mgmt_levels AS ( SELECT jobholder_uid, positionmgr_uid, position_level, bu_div FROM positions WHERE positionmgr_uid = '' UNION ALL SELECT p.jobholder_uid, p.positionmgr_uid, p.position_level, p.bu_div FROM positions p INNER JOIN mgmt_levels ml ON ml.jobholder_uid = p.positionmgr_uid ) SELECT jobholder_uid FROM mgmt_levels ml );

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.