CODEWITHSUNDEEP
sqlsql-serversql-update
DaleP
DaleP

Reputation: 53

SQL Update with AVG of Joined Tables

I'm trying to update the supplier lead time in a table by calculating the difference between date of order and stock receipt date....

UPDATE cr_accs 
SET    cr_accs.leadtime = Avg(Datediff(day, purchord_hdr.orderdate, 
                                     stock_trans.transdate)) 
FROM   stock_items 
       INNER JOIN stock_trans 
               ON stock_trans.stockcode = stock_items.stockcode 
       INNER JOIN purchord_hdr 
               ON purchord_hdr.seqno = stock_trans.ref1 
WHERE   cr_accs.accno = purchord_hdr.accno 
       AND stock_trans.location = 1 
       AND stock_trans.ref2 = 'RECEIPT'
       AND purchord_hdr.orderdate >= Dateadd(day, Datediff(day, 0, Getdate()),-730) 
       AND stock_items.isactive = 'Y' 
       AND stock_items.bincode NOT IN ( 'OIO', 'CON' )

However I'm getting an error

An aggregate may not appear in the set list of an UPDATE statement

I've seen other solutions where you would change the query to:

UPDATE cr_accs 
SET    cr_accs.leadtime = h.calc_lead_time
FROM   (SELECT AVG(DATEDIFF(day, purchord_hdr.orderdate, stock_trans.transdate)) AS calc_lead_time  
        FROM stock_items 
       INNER JOIN stock_trans 
               ON stock_trans.stockcode = stock_items.stockcode 
       INNER JOIN purchord_hdr 
               ON purchord_hdr.seqno = stock_trans.ref1  
       INNER JOIN cr_accs 
               ON cr_accs.accno = purchord_hdr.accno 
WHERE   cr_accs.accno = purchord_hdr.accno  
       AND stock_trans.location = 1 
       AND stock_trans.ref2 = 'RECEIPT' 
       AND purchord_hdr.orderdate >= Dateadd(day, Datediff(day, 0, Getdate()),-730) 
       AND stock_items.isactive = 'Y' 
       AND stock_items.bincode NOT IN ( 'OIO', 'CON' ) ) h

However this is not the solution for me as it doesn't define that the lead time is unique to each supplier... It may be helpful to point out that each supplier is identified by cr_accs.accno

Any ideas please?

Upvotes: 1

Views: 125

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

You can also do what you want using a correlated subquery, which might be what you were trying to do:

UPDATE cr_accs 
    SET cr_accs.leadtime =
         (SELECT AVG(DATEDIFF(day, p.orderdate, st.transdate)) AS calc_lead_time  
          FROM stock_items si JOIN
               stock_trans st
               ON st.stockcode = si.stockcode JOIN
               purchord_hdr p
               ON p.seqno = st.ref1  
          WHERE cr_accs.accno = p.accno  AND
                st.location = 1 AND
                st.ref2 = 'RECEIPT' AND
                p.orderdate >= Dateadd(day, Datediff(day, 0, Getdate()), -730) AND
                si.isactive = 'Y' AND
                si.bincode NOT IN ( 'OIO', 'CON' )
         );

I introduced table aliases so the query is easier to write and to read.

SQL Server also gives you the ability to express this using APPLY:

UPDATE a 
    SET a.leadtime = p.calc_lead_time
    FROM cr_accs a CROSS APPLY
         (SELECT AVG(DATEDIFF(day, p.orderdate, st.transdate)) AS calc_lead_time  
          FROM stock_items si JOIN
               stock_trans st
               ON st.stockcode = si.stockcode JOIN
               purchord_hdr p
               ON p.seqno = st.ref1  
          WHERE a.accno = p.accno  AND
                st.location = 1 AND
                st.ref2 = 'RECEIPT' AND
                p.orderdate >= Dateadd(day, Datediff(day, 0, Getdate()), -730) AND
                si.isactive = 'Y' AND
                si.bincode NOT IN ( 'OIO', 'CON' )
         ) p;

Upvotes: 1

forpas
forpas

Reputation: 164174

Try this join of the table to the query:

UPDATE c
SET c.leadtime = h.calc_lead_time
FROM cr_accs c 
INNER JOIN (
  SELECT purchord_hdr.accno,
    AVG(DATEDIFF(day, purchord_hdr.orderdate, stock_trans.transdate)) AS calc_lead_time  
  FROM stock_items 
  INNER JOIN stock_trans ON stock_trans.stockcode = stock_items.stockcode 
  INNER JOIN purchord_hdr ON purchord_hdr.seqno = stock_trans.ref1  
  WHERE      stock_trans.location = 1 
         AND stock_trans.ref2 = 'RECEIPT' 
         AND purchord_hdr.orderdate >= Dateadd(day, Datediff(day, 0, Getdate()),-730) 
         AND stock_items.isactive = 'Y' 
         AND stock_items.bincode NOT IN ('OIO', 'CON') 
  GROUP BY purchord_hdr.accno
) h ON h.accno = c.accno

I assume (by your code and the error message) that you are using SQL Server.

Upvotes: 1

Related Questions