tag:blogger.com,1999:blog-18730743.post8026766799615397586..comments2024-02-24T21:55:43.157+01:00Comments on SQL Troubles: SQL Reloaded: Self-Join in Update Query IAdrianhttp://www.blogger.com/profile/07612735744573114626noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-18730743.post-28762776469059365562011-06-30T09:08:04.070+02:002011-06-30T09:08:04.070+02:00You'll have to move the aggregation inside the...You'll have to move the aggregation inside the inline view. Something like:<br /><br />update testtable <br />SET tot_qty =itm.qty_A + itm.qty_B<br />, tot_qty_B = itm.qty_B<br />FROM ( -- inline view <br />SELECT itm.company_num<br />, sum(itm.qty_A) qty_A<br />, sum(itm.qty_B) qty_B<br />FROM testtable ITM <br />GROUP BY itm.company_num<br />) itm <br />where testtable.company_num = Adrianhttps://www.blogger.com/profile/07612735744573114626noreply@blogger.comtag:blogger.com,1999:blog-18730743.post-66344779089352321442011-06-30T07:32:45.494+02:002011-06-30T07:32:45.494+02:00thanks for the post.I just wondering how to apply ...thanks for the post.I just wondering how to apply this in group function update?Is it allowed in oracle ? see my sample update below, it throw me an error 'group function is not allowed'<br /><br />update testtable <br />SET tot_qty =itm.qty_A + sum(itm.qty_B) , tot_qty_B =sum(itm.qty_B)<br />FROM ( -- inline view <br /> SELECT * <br /> FROM testtable ITM <br /> ) itm <br />whereunidhttps://www.blogger.com/profile/09929503035044413506noreply@blogger.com