February 13, 2014 at 11:01 am
Hi Everyone.
This is my code.
update co set co.Uf_CoCompletedDate = MAX(inv_hdr.inv_date)
FROM CO
join inv_hdr on inv_hdr.co_num = co.co_num
where co.Uf_CoCompletedDate IS NULL
And this is the error message...
An aggregate may not appear in the set list of an UPDATE statement.
Does anyone know a way around this?
Thank you in advance for your help.
February 13, 2014 at 11:10 am
There's not enough there to actually write you a statement, but I'll give you the layout:
UPDATE co
SET fielda = sumFieldb
FROM
Table1 AS co
JOIN
(SELECT
keyfield,
SUM(something) AS sumFieldb
FROM
tabl2
GROUP BY
keyfield
) AS drv
ON co.keyfield = drv.keyfield
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 13, 2014 at 11:27 am
Okay Craig, thank you I will try it.
I am just trying to update a customer order field with a max invoice date from the invoice header. I didn't know you could use a sum on a date field.
Thanks.
G
February 13, 2014 at 11:36 am
Gillian_Pappas2002 (2/13/2014)
I am just trying to update a customer order field with a max invoice date from the invoice header. I didn't know you could use a sum on a date field.
WAAAAIIIIITTTTT.
You can, but don't!
Swap my SUM out for MAX. I was just quickly putting up a format, not trying to write up your code for you. If I'm going to put up tested code I prefer sample schemas and the like. That was just an example of how to do it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 13, 2014 at 11:42 am
Thank you Craig.
Gillian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply