Sql Server Programming :: Updating new column using a subquery


	Updating new column using a subquery I have a table Regions with the RegionID and RegionState. I am trying to add a new column so that when I run my query I get a total sales for that region. I understand I have to use a correlated query but I am not making the connection between the inner query to the outer query in order to pass the TotalSales. ALTER TABLE Regions ADD TotalSales money not null default 0 BEGIN Transaction UPDATE Regions SET TotalSales=(SELECT Sum (QtyPrice)FROM OrderItems WHERE R.RegionID=RS.RegionID SELECT R.RegionID,R.RegionName FROM Customers C INNER JOIN Orders O ON C.CustomerID=O.CustomerID INNER JOIN OrderItems OI ON O.InvoiceID=OI.InvoiceID INNER JOIN RegionStates RS ON C.State=RS.State INNER JOIN Regions R ON RS.RegionID=R.RegionID Group by R.RegionID,R.RegionName ROLLBACK Transaction UPDATE r SET TotalSales = sub.TotalSales FROM Regions r INNER JOIN ( SELECT RegionID, TotalSales = SUM(QtyPrice) FROM OrderItems GROUP BY RegionID ) sub ON r.RegionID = sub.RegionID I'm not sure why you brought in all that extra stuff, from customers, regionstates, etc. I'm also not sure why you want to store this data when it will be much better to calculate it at runtime. The information is redundant and if any row changes you will need to re-calculate. -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 "RuthE" <R@discussions.microsoft.com> wrote in message news:2415B464-09E9-4B8F-95B2-10197794B95F@microsoft.com... - Hide quoted text - >I have a table Regions with the RegionID and RegionState. I am trying to >add > a new column so that when I run my query I get a total sales for that > region. > I understand I have to use a correlated query but I am not making the > connection between the inner query to the outer query in order to pass the > TotalSales. > ALTER TABLE Regions > ADD TotalSales money not null default 0 > BEGIN Transaction > UPDATE Regions > SET TotalSales=(SELECT Sum (QtyPrice)FROM OrderItems > WHERE R.RegionID=RS.RegionID > SELECT R.RegionID,R.RegionName > FROM Customers C > INNER JOIN Orders O > ON C.CustomerID=O.CustomerID > INNER JOIN OrderItems OI > ON O.InvoiceID=OI.InvoiceID > INNER JOIN RegionStates RS > ON C.State=RS.State > INNER JOIN Regions R > ON RS.RegionID=R.RegionID > Group by R.RegionID,R.RegionName > ROLLBACK Transaction Aarton Thanks for your quick response. I am new to SQL which is why I was trying to do all of the joins. I'm having a hard time understanding how a value is returned back to the main query. Can you explain or refer me to a link that might explain further? In my case I did the join because otherwise there is nothing to link the Regions table to the OrderItems table. "Aaron Bertrand [SQL Server MVP]" wrote: - Hide quoted text - > UPDATE r > SET TotalSales = sub.TotalSales > FROM Regions r > INNER JOIN > ( > SELECT RegionID, > TotalSales = SUM(QtyPrice) > FROM OrderItems > GROUP BY RegionID > ) sub > ON r.RegionID = sub.RegionID > I'm not sure why you brought in all that extra stuff, from customers, > regionstates, etc. > I'm also not sure why you want to store this data when it will be much > better to calculate it at runtime. The information is redundant and if any > row changes you will need to re-calculate. > -- > Aaron Bertrand > SQL Server MVP > http://www.sqlblog.com/ > http://www.aspfaq.com/5006 > "RuthE" <R@discussions.microsoft.com> wrote in message > news:2415B464-09E9-4B8F-95B2-10197794B95F@microsoft.com... > >I have a table Regions with the RegionID and RegionState. I am trying to > >add > > a new column so that when I run my query I get a total sales for that > > region. > > I understand I have to use a correlated query but I am not making the > > connection between the inner query to the outer query in order to pass the > > TotalSales. > > ALTER TABLE Regions > > ADD TotalSales money not null default 0 > > BEGIN Transaction > > UPDATE Regions > > SET TotalSales=(SELECT Sum (QtyPrice)FROM OrderItems > > WHERE R.RegionID=RS.RegionID > > SELECT R.RegionID,R.RegionName > > FROM Customers C > > INNER JOIN Orders O > > ON C.CustomerID=O.CustomerID > > INNER JOIN OrderItems OI > > ON O.InvoiceID=OI.InvoiceID > > INNER JOIN RegionStates RS > > ON C.State=RS.State > > INNER JOIN Regions R > > ON RS.RegionID=R.RegionID > > Group by R.RegionID,R.RegionName > > ROLLBACK Transaction You're just joining against the subquery. You should see by running it on its own, there is exactly one row per region. This is what you are joining against when you run the update. SELECT RegionID, TotalSales = SUM(QtyPrice) FROM OrderItems GROUP BY RegionID -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 "RuthE" <R@discussions.microsoft.com> wrote in message news:6468F439-C77B-49AC-ACA7-6CAF68A3C11D@microsoft.com... - Hide quoted text - > Aarton > Thanks for your quick response. I am new to SQL which is why I was trying > to > do all of the joins. I'm having a hard time understanding how a value is > returned back to the main query. Can you explain or refer me to a link > that > might explain further? In my case I did the join because otherwise there > is > nothing to link the Regions table to the OrderItems table. >> I have a table Regions with the region_id and region_sate. I am trying to add a new column so that when I run my query I get a total sales for that region.<< Why are you adding a computed column to a table ??!! Why did you use MONEY -- a non-ANSI data type with mathematical problems, of the love of Ghod!! Real SQL programmers do it with a VIEW that is always correct. Write a VIEW!! CREATE VIEW RegionWithTotals AS SELECT <<no idea because of crappy specs >>, FROM Regions WHERE .. ; Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Just basic netiquette, please. We are doing your job for you and you should show us basic Netiquette. Oh, you need to read and learn ISO-11179 naming rules and how to format SQL. You have just been told by someone who helped write that your code and approach stink. Learn, before you hurt or kill someone. -----------------------------------------------Reply----------------------------------------------- "--CELKO--" <jcelko@earthlink.net> wrote in message news:1181068569.848068.290610@w5g2000hsg.googlegroups.com.. > Real SQL programmers do it with a VIEW that is always correct. Write a > VIEW!! Real SQL programmers should stick to their sandbox and recuse themselves from application development. You flash ignorance like a streaker. > Oh, you need to read and learn ISO-11179 naming rules and how to > format SQL. You have just been told by someone who helped write that > your code and approach stink. You want credit for something a mile wide and an inch deep. And one still needs boots. www.beyondsql.blogspot.com