Need assistance with SQL Query with error message Operand should contain 1 column(s)

Question

Good Day

I have the following query but I'm getting an error message 'Operand should contain 1 column(s)'

Any assistance would be greatly appreciated

UPDATE expenditure
SET BP = (
    SELECT * ,
           SUM(balance_provision - actual_amt_voucher) over (partition by voteid order by expenditureid) AS BalanceProvision
    FROM expenditure
)

Answer 1

It looks like you want to update column bp with a window sum.

Your query fails because you are trying to assign a resultset (that has multiple columns) to a single column.

But even you were returning a scalar value from the subquery, this would not work, since MySQL does not allow reusing the target table of the update in a subquery.

Instead, yo can use the update ... join syntax. Assuming that expenditureid is the primary key of the table, as its name suggests, that would be:

update expenditure e
inner join (
    select 
        expenditureid, 
        sum(balance_provision - actual_amt_voucher) over (partition by voteid order by expenditureid) bp
    from expenditure
    group by expenditureid
) e1 on e.expenditureid = e1.expenditureid
set e.bp = e1.bp