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

Revision: 111269
User: Neil Dempster
Date: 2021-11-08 13:52

I have a recursive query that works perfectly. Here it is:
<<code>>
WITH RECURSIVE mgmt_levels AS ( SELECT jobholder_uid, positionmgr_uid, position_level FROM positions WHERE positionmgr_uid = 'foo.bar@company.com'
UNION ALL
SELECT p.jobholder_uid, p.positionmgr_uid, p.position_level FROM positions p INNER JOIN mgmt_levels ml ON ml.jobholder_uid = p.positionmgr_uid )
SELECT jobholder_uid, positionmgr_uid, position_level
FROM mgmt_levels ml;
<</code>>

However, if I now want to perform an 'UPDATE' to the output from the original (successful) query, I get an error (#1241 - Operand should contain 1 column(s)). I looked up this error but I don't see where I have made the error. What am I missing here? Thank you! Here is my UPDATE code ...

<<code>>
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 = 'foo.bar@company.com'
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, positionmgr_uid, position_level, bu_div
FROM mgmt_levels ml
);
<</code>>

Answer

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.

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

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB