We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


updates with aggregates — Vertica Forum

updates with aggregates

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

Comments

  • 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.
  • 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?
  • It's erroring out. It seems like updates do not like subqueries that are using comparisons. Is there another way to do this query?
  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file