Update with inner join = Error Code: 1111. Invalid use of group function

Question

I am getting the 1111 error in the post title when executing the query below. Basically, I am try to update table 1 (special_valuation_temp) with data from table 2 (search_upload_quotes). I want to get the minimum and maximum quote values to update table 1 with.

UPDATE special_valuation_temp svt 
       INNER JOIN search_upload_quotes suq 
               ON ( svt.clei = suq.clei 
                     OR svt.partnumber = suq.partnumber ) 
SET    svt.vendor_low = ( Min(suq.priceperunit) * svt.qty ), 
       svt.vendor_high = ( Max(suq.priceperunit) * svt.qty ) 
WHERE  suq.submitted = 1 
       AND suq.priceperunit > 0;

It seems that I can't use the MIN() and MAX() functions in the SET clause. Is there another way to do this?

Answer 1

Yes, that's incorrect without the use of group by. You can rather get the min() and max() value first in a subquery and perform a join with that subquery result and do your calculation as-is.