CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Justin
Justin

Reputation: 269

Aggregate may not appear in the set list of an UPDATE statement

Receiving an error stating an aggregate may not appear in the set list of an UPDATE statement. Any ideas?

 UPDATE STOCK SET STOCK.LPURCHDATE = MAX(INVTRANS.TRANS_DATE)
 FROM STOCK
 INNER JOIN INVTRANS ON STOCK.NUMBER = INVTRANS.NUMBER
 WHERE INVTRANS.NOTATION <> 'Product Merging' AND
(INVTRANS.TRANSTYPE = 'D' OR INVTRANS.TRANSTYPE = 'A')

Upvotes: 0

Views: 8051

Answers (1)

Ravi
Ravi

Reputation: 31407

Try This

UPDATE STOCK SET STOCK.LPURCHDATE = (select MAX(INVTRANS.TRANS_DATE) FROM INVTRANS
                                WHERE INVTRANS.NOTATION <> 'Product Merging' 
                                AND  INVTRANS.TRANSTYPE IN('D','A') 
                                AND STOCK.NUMBER = INVTRANS.NUMBER)

OR

;with tmp as(
SELECT STOCK.NUMBER, MAX(INVTRANS.TRANS_DATE) MX
 FROM STOCK
 INNER JOIN INVTRANS ON STOCK.NUMBER = INVTRANS.NUMBER
 WHERE INVTRANS.NOTATION <> 'Product Merging' AND
(INVTRANS.TRANSTYPE = 'D' OR INVTRANS.TRANSTYPE = 'A')
)
 UPDATE STOCK SET STOCK.LPURCHDATE = MX
 FROM STOCK
 INNER JOIN TMP ON STOCK.NUMBER = TMP.NUMBER

Upvotes: 2

Related Questions