Can you combine Update/Sum Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you combine Update/Sum Query?

Author

Topic

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-04 : 05:54:23

Hi All,

I am trying to write a SQL statement query, that will calculate the SUM value in one table for a March period, and update that value to another table, here is what I have so far, but the compile error says Operation must use an updatable query.

UPDATE League_Totals SET League_Totals.March_Total = (SELECT SUM(Result_Value)
FROM Result_Details
WHERE LEAP_Date = "March_2014");

Any ideas what I am doing wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 05:59:23

what is the relationship between the League_Totals an Result_Details ?

KH
[spoiler]Time is always against us[/spoiler]

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-04 : 06:06:33

There is no direct relationship between these tables, they both are linked to LEAP_date in LEAP table and Store_Number in Store information table, do they need to be linked directly to run his query?

quote:
Originally posted by khtan

what is the relationship between the League_Totals an Result_Details ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 06:10:54

[code]
UPDATE LT
SET March_Total = RD.March_Total
FROM League_Totals LT
INNER JOIN
(
SELECT Store_Number, March_Total = SUM ( March_Total )
FROM Result_Details
WHERE LEAP_Date = 'March_2014'
GROUP BY Store_Number
) RD ON LT.Store_Number = RD.Store_Number
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-04 : 06:13:02

I have updated the relationships, so that League_Table and Reults_Details have a one to many relationship, but query will still not work?

quote:
Originally posted by cazziewhelan

There is no direct relationship between these tables, they both are linked to LEAP_date in LEAP table and Store_Number in Store information table, do they need to be linked directly to run his query?

quote:
Originally posted by khtan

what is the relationship between the League_Totals an Result_Details ?

KH
[spoiler]Time is always against us[/spoiler]

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-04 : 06:22:06

Thak you :)

I have tried to run this as a SQL query, but recieveing a syntax error, saying I am missing an opeator in query expression?

quote:

Originally posted by khtan


UPDATE LT
SET    March_Total = RD.March_Total
FROM   League_Totals LT
       INNER JOIN
       (
               SELECT Store_Number, March_Total = SUM ( March_Total )
               FROM   Result_Details
               WHERE LEAP_Date = 'March_2014'
               GROUP BY Store_Number
       ) RD  ON LT.Store_Number = RD.Store_Number

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-04 : 06:22:32

quote:
Originally posted by cazziewhelan

There is no direct relationship between these tables, they both are linked to LEAP_date in LEAP table and Store_Number in Store information table, do they need to be linked directly to run his query?

quote:
Originally posted by khtan

what is the relationship between the League_Totals an Result_Details ?

KH
[spoiler]Time is always against us[/spoiler]

then you need to use the intermedaite tables also to link them correctly in above update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources