CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
LukeM
LukeM

Reputation: 13

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

In T-SQL I'm attempting to update a stock user field with the number of weeks we expect it to be delivered to us by taking the difference between today and the purchase order due in dates. However the select query can return more than one line of purchase orders if there is more than one purchase order containing that product (obviously). I would like to take the smallest number it returns / minimum value but obviously cannot do this within the update query. Can anyone recommend a workaround? Thanks.

UPDATE [Exchequer].[ASAP01].[STOCK]
SET stUserField7 = DATEDIFF(day,CONVERT(VARCHAR(8), GETDATE(), 112),min(tlLineDate)) / 7 + 1
FROM [Exchequer].[ASAP01].[STOCK]
JOIN [Exchequer].[ASAP01].[CUSTSUPP]
ON stSupplier = acCode
JOIN [Exchequer].[ASAP01].[DETAILS]
ON stCode = tlStockCodeTrans1
WHERE stSupplier <> '' AND stQtyOnOrder > '0' AND stQtyOnOrder > stQtyAllocated 
AND tlOurRef like 'POR%' AND (floor(tlQtyDel) + floor(tlQtyWOFF)) < floor(tlQty) 
AND tlLineDate >= CONVERT(VARCHAR(8),GETDATE(), 112)

Upvotes: 1

Views: 178

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Why are you casting date to varchar for the difference?

This is not date but how you can use a window function in an update 

declare @maps table(name varchar(10), isUsed bit, code varchar(10));
insert into @Maps values 
       ('NY', 1, 'NY1')
     , ('NY', 0, 'NY2')
     , ('FL', 0, 'FL1')
     , ('TX', 0, 'TX1')
declare @Results table (id int identity primary key, Name varchar(20), Value int, Code varchar(20), cnt int)
insert into @results values
       ('FL', 12, 'FL1', null)
     , ('TX', 54, 'TX1', null)
     , ('TX', 56, 'TX1', null)
     , ('CA', 50, 'CA1', null)
     , ('NJ', 40, 'NJ1', null)

select * from @results 
order by name, Value desc

update r
set r.cnt = tt.cnt
from @results r 
join ( select id, max(value) over (partition by name) as cnt
       from @Results 
     ) tt 
on r.id = tt.id

select * from @results 
order by name, value desc

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416053

Build a SELECT query with columns for the following:

  1. The primary key of the [Exchequer].[ASAP01].[STOCK] table
  2. The new desired stUserField7

Given the MIN(tlLineDate) expression in the original question, if this SELECT query does not have either a GROUP BY clause or change to use an APPLY instead of a JOIN, you've probably done something wrong.

Once you have that query, use it in an UPDATE statement like this:

UPDATE s
SET s.stUserField7 = t.NewValueFromSelectQuery
FROM [Exchequer].[ASAP01].[STOCK] s
INNER JOIN (
    --- your new SELECT query here
) t ON t.<primary key field(s)> = s.<primary key field(s)>

Upvotes: 1

Related Questions