updates with aggregates

ed_yeh

I need to do an update with an aggregate, and vsql is having a problem: eg: update tempdb.table1 set total_units = (select sum (sales_units) from tempdb.table2 m2 where tempdb.table1.sales_event_id = md2.sales_event_id and m2.full_dt <= table1.full_dt ); any ideas? thanks

[Deleted User]

Hi Ed, thanks for your question! We'll see if anyone has some insight as to why this code doesn't seem to be working.

[Deleted User]

Hi Ed, could you provide some more detail in your post? "is having a problem" is very general... Maybe you could include the error message that you're seeing? And what are the schemas of the four tables that you're querying?

ed_yeh

It's erroring out. It seems like updates do not like subqueries that are using comparisons. Is there another way to do this query?

[Deleted User]

Well, yes, you indicated before that it's erroring out. I was looking for some more detail, as per my last message. Could you please post that information? This is perhaps a point of general forum etiquette (not just here but in any online forum): The more specific your post, the more likely you are to get a useful answer. Something like "is having a problem" or "is erroring out" is extremely general, and unlikely to be found by anyone who knows the answer. Something like the exact text of an error message, or a copy/paste of a vsql session that illustrates the setup and query and error, is much more likely to be found and to get a response. (People will be more likely to read it; also, search engines will index it, and others with similar errors will find it and be able to respond.) You did include the query, which is good. But I don't know the definitions of those tables. And if I make up some schema and run that query against it, it runs just fine. So I really will need more detail in order to help you.