CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

using average function in an update statement

I have the query below which works.

  select m.ActionId, 
         TargetTicker, 
         AVG(p.price)
    from tblMADeals m left join 
         tblMAPrices p on m.ActionId = p.ActionId and m.TargetTicker = p.Ticker
   where Ignore = 0 
group by m.ActionId, 
         m.TargetTicker
order by AVG(p.price)

What I want to do in my next query is update 2 columns in the table tblMADeals where the average price is 0. However SQL doesn't like me update statement.

 update m
    set m.Ignore = 1, 
        m.Note = 'no prices target'
   from tblMADeals m left join 
        tblMAPrices p on m.ActionId = p.ActionId and m.TargetTicker = p.Ticker
  where m.Ignore = 0 and 
        AVG(p.price) = 0

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

So I'm not sure how to adjust my query

Upvotes: 0

Views: 182

Answers (2)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You need to use a CTE to do something like this:

with avgprices as (
 select m.ActionId, 
         TargetTicker, 
         AVG(p.price) as avgprice
    from tblMADeals m left join 
         tblMAPrices p on m.ActionId = p.ActionId and m.TargetTicker = p.Ticker
   where Ignore = 0 
group by m.ActionId, 
         m.TargetTicker

)

update m set m.ignore = 1,m.note = 'no prices target'
from tablmadeals m left join avgprices a on a.actionid=m.actionid and a.targetpicker = m.targetpicker
where avgprice = 0 and m.ignore  = 0

Upvotes: 2

Ven
Ven

Reputation: 2014

Cte with inner join on table to udpate table 

WITH cte
AS (
    SELECT m.ActionId ActionId
        ,TargetTicker TargetTicker
        ,AVG(p.price) price
    FROM tblMADeals m
    LEFT JOIN tblMAPrices p
        ON m.ActionId = p.ActionId
            AND m.TargetTicker = p.Ticker
    WHERE Ignore = 0
    GROUP BY m.ActionId
        ,m.TargetTicker
    )
UPDATE m
SET m.Ignore = 1
    ,m.Note = 'no prices target'
FROM tblMADeals m
INNER JOIN cte c
    ON m.ActionId = c.ActionId
        AND m.TargetTicker = c.TargetTicker
WHERE m.Ignore = 0
    AND (p.price) = 0

Upvotes: 0

Related Questions