HOW TO USE A SUM FUNCTION IN A UPDATE QUERY

Question

HOW TO USE A SUM FUNCTION IN A UPDATE QUERY

ian_dunlop2000

SSC-Addicted

Points: 430
More actions
April 1, 2008 at 7:33 am

#204985

HI
I am trying to use an update query to sum MAT_GWP when it is between the dates stated in the below statement - its DRVING ME CRAZY - as I don't think it is far from being correct - any hel;p would be great
UPDATE GOLD_BROKER_OUTPUT
SET MAT_GWP = (select sum (CASE WHEN dbo.tblOneStop_Detail.DAT_YEAR_MONTH BETWEEN 200703 AND 200802
THENdbo.tblOneStop_Detail.GWP
ELSE 0
END))
FROM GOLD_BROKER_OUTPUT CROSS JOIN tblOneStop_Detail
group by MAT_GWP
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

How about post the DDL for the tables, some sample data for each table (in the form of an insert statement with union all select statements), and what the output (results) of the query should be when run for the sample data.

That would help alot.

😎

steveb. SSC-Forever Points: 46733 More actions · Answer 2

You can't use Group By as part of an Update query, if it meant to be part of the sub-query then you should enclose it in ')'.

You should break the query into two parts. in the first create a temp table to put the values in, and then another query to update the GOLD_BROKER_OUTPUT table with the values from the temp table.

but without seeing the table structure and some data it is hard to give any more specific advice.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Definitely need the table definitions to solve this one. With those, will only take a minute or two.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Ian, always check an UPDATE...FROM using a SELECT first. Only when the SELECT returns the values you are expecting should you convert it over to an UPDATE...FROM. It makes the statement far easier to check. Your statement will be something like this...

-- Make some test data

drop table #tester

create table #tester ([id] int, testcol int)

insert into #tester ([id], testcol)

select 1, 0 union all

select 2, 0 union all

select 3, 0

drop table #testersum

create table #testersum ([id] int, testcol int)

insert into #testersum ([id], testcol)

select 1, 1 union all

select 2, 1 union all

select 3, 10 union all

select 3, 10

------------------------------------------------------------------------

-- test without update

SELECT t.[id], d.Sumtestcol

FROM #tester t

INNER JOIN (SELECT [id], SUM(testcol) AS Sumtestcol FROM #testersum GROUP BY [id]) d

ON d.[id] = t.[id]

-- run with update

UPDATE t SET testcol = d.Sumtestcol

FROM #tester t

INNER JOIN (SELECT [id], SUM(testcol) AS Sumtestcol FROM #testersum GROUP BY [id]) d

ON d.[id] = t.[id]

-- check results

SELECT * FROM #tester

...but as the guys have pointed out, without more information it's impossible to tell.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

The inner query is missing a FROM, and the group by would need to move into it. You should then make it a derived table and move it to the FROM statement of the outer query.

Also - a sum against a cross join? How is that supposed to give you accurate results?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?