0

I have a table with data of different months. I want to update 'PUC_AVG_UCOST' with data from the same table where the date is max date My sql is as follows

update  lty_prd_u_cost a
    set PUC_AVG_UCOST= 
        ( SELECT  PUC_AVG_UCOST
            from  lty_prd_u_cost b
            where  PUC_DATE=
                ( SELECT  max(c.PUC_DATE)
                    from  lty_prd_u_cost c
                    where  b.PUC_PRD_NAME=c.PUC_PRD_NAME
                ) 
        )
    where  a.PUC_AVG_UCOST=0
      and  puc_date='2021-08-31'

There is the following error

[Err] 1242 - Subquery returns more than 1 row

Kindly help me correct it

enter image description here

enter image description here

IF I can not find a value for the current month the value of the immediate past month will be taken as the current month Value.

Improve this question
9
  • 1
    Sounds like there are 2 rows on the last day. Show some of the data so we can point that out. Commented Oct 4, 2021 at 16:47
  • Could you help me with a solution please Commented Oct 5, 2021 at 3:33
  • That's the data before the UPDATE, correct? What should the data look like after? Commented Oct 5, 2021 at 5:04
  • I have give a SS before and after update Commented Oct 5, 2021 at 7:03
  • Since the names is "AVG", does that mean "average". If so, why isn't the value (2300+0)/2 = 1150 ? Commented Oct 5, 2021 at 20:57

1 Answer 1

Reset to default
2

This query doesn't work because the only circumstance in which a subquery will work in the SET statement is if it produces a single value (like, (SELECT max(PUC_DATE)) would just be 2021-08-31). As it's written, the subquery produces a row for every PUC_PRD_NAME. And it's asking it to write ALL these rows in the subquery for EACH row in the UPDATE statement, which is impossible.

Try joining the subquery (which I've modified) to the UPDATE statement:

UPDATE lty_prd_u_cost a
LEFT JOIN (
    SELECT PUC_PRD_NAME, PUC_AVG_UCOST
    FROM (
        SELECT PUC_PRD_NAME, PUC_AVG_UCOST
            ,row_number() over (partition by PUC_PRD_NAME order by PUC_DATE desc) as rn
        FROM lty_prd_u_cost
        ) b0
    WHERE b0.rn = 1
) b
ON a.PUC_PRD_NAME = b.PUC_PRD_NAME
SET a.PUC_AVG_UCOST = b.PUC_AVG_UCOST
WHERE a.PUC_AVG_UCOST=0
    and a.PUC_DATE = '2021-08-31'
Improve this answer
2
  • It gives the following error message [Err] 1054 - Unknown column 'PUC_PRD_NAME' in 'field list' Commented Oct 5, 2021 at 4:01
  • @ypercubeᵀᴹ fixed it- I forgot to include FROM lty_prd_u_cost in the inner subquery. Commented Oct 5, 2021 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.