June 10, 2011 at 5:37 am
Hi All,
I am trying to update table with group by function but i am getting following error:
Error: Executing the query "
update Accounts_Fact set Create_Date=MIN(Create_D..." failed with the following error: "An aggregate may not appear in the set list of an UPDATE statement.".
My query is
update Accounts_Fact set Create_Date=MIN(Create_Date)
where AccountID = ( select AccountID from Accounts_Fact group by AccountID)
Please let me know what to do to resolve this issue?
June 10, 2011 at 5:49 am
The error message is quite clear , you will need to perform the aggregation in another block such as a CTE and then join back to table you want to update to.
something like
with cte as (
select col1, min(col2) as col2 from tb1 group by col1)
update u
set col1 = c.col2
from tb2 u
join cte c
on u.col1 = c.col1
June 10, 2011 at 6:44 am
Thanks man...it worked 🙂
June 13, 2011 at 12:53 am
I am using getdate() function for create_date column, so whenever i will execute my ETL create date will set to get date, but i need first create_date to be in create_date column, so finding minimum create date of each account , thats why i have set it to group by accountid and this will help me to set create date to minimum create date for each account. 🙂
June 13, 2011 at 8:01 am
Do not use the proprietary UPDATE.. FROM.. syntax; it has cardinality problems and will not port. But it looks weird to me; why do you want to set every creation date to the same value?
Could you explain what you mean by "cardinality problems", please?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply