Updating a table using SUM (with multiple values)

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

SQL Server 2005 Forums

Transact-SQL (2005)

Updating a table using SUM (with multiple values)

Author

Topic

scuzzles
Starting Member

11 Posts

Posted - 2007-07-25 : 15:56:59

Hello. Im sorta new with this. I need to take multiple records for an employee in AUDMONTH and add them into the values already in AUDMAST which has 1 employee record, which is the primary key.

The below statement works only when theres one record from AUDMONTH. If theres a few records, I need the total sum of those records in AUDMONTH into the existing record in AUDMAST. In other words, the pluses dont add up the total sum for me.

UPDATE AUDMAST

SET Y_DIFF = AUDMAST.Y_DIFF + AUDMONTH.M_DIFF, Y_ADJ = AUDMAST.Y_ADJ + AUDMONTH.M_ADJ, Y_OVER = AUDMAST.Y_OVER + AUDMONTH.M_OVER, Y_SHORT = AUDMAST.Y_SHORT + AUDMONTH.M_SHORT, Y_NET = AUDMAST.Y_NET + AUDMONTH.M_NET, M_DIFF = AUDMAST.M_DIFF + AUDMONTH.M_DIFF, M_ADJ = AUDMAST.M_ADJ + AUDMONTH.M_ADJ, M_OVER = AUDMAST.M_OVER + AUDMONTH.M_OVER, M_SHORT = AUDMAST.M_SHORT + AUDMONTH.M_SHORT, M_NET = AUDMAST.M_NET + AUDMONTH.M_NET, EMP_NO = AUDMONTH.EMP_NO

FROM AUDMONTH INNER JOIN
AUDMAST ON AUDMONTH.EMP_NO = AUDMAST.EMP_NO

After doing some research I read to use Select SUM for those AUDMONTH records and then join them with AUDMAST table. But I dont know how to add the other Select Sums to this...This example is for one field.

UPDATE AUDMAST
SET Y_DIFF = Y_DIFF +
(SELECT SUM(AUDMONTH.M_DIFF) AS Expr1
FROM AUDMONTH INNER JOIN
AUDMAST AS AUDMAST_1 ON AUDMONTH.EMP_NO = AUDMAST_1.EMP_NO CROSS JOIN
AUDMAST AS AUDMAST_2)

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:12:59

[code]UPDATE am
SET am.Y_DIFF = am.Y_DIFF + x.M_DIFF,
am.Y_ADJ = am.Y_ADJ + x.M_ADJ,
am.Y_OVER = am.Y_OVER + x.M_OVER,
am.Y_SHORT = am.Y_SHORT + x.M_SHORT,
am.Y_NET = am.Y_NET + x.M_NET,
am.M_DIFF = am.M_DIFF + x.M_DIFF,
am.M_ADJ = am.M_ADJ + x.M_ADJ,
am.M_OVER = am.M_OVER + x.M_OVER,
am.M_SHORT = am.M_SHORT + x.M_SHORT,
am.M_NET = am.M_NET + x.M_NET
FROM AUDMONTH AS am
INNER JOIN (
SELECT EMP_NO,
SUM(M_DIFF) AS M_DIFF,
SUM(M_ADJ) AS M_ADJ,
SUM(M_OVER) AS M_OVER,
SUM(M_SHORT) AS M_SHORT,
SUM(M_NET) AS M_NET
FROM AUDMAST
GROUP BY EMP_NO
) AS x ON x.EMP_NO = am.EMP_NO[/code]

E 12°55'05.76"
N 56°04'39.42"

scuzzles
Starting Member

11 Posts

Posted - 2007-07-25 : 16:27:23

Wow, that was quick. I CUT AND PASTED this code and checked this SQL and I got invalid column name for 'Y_DIFF','Y_ADJ','Y_OVER', 'Y_SHORT', 'Y_NET'. Those are correct field names so something isnt right with the syntax above.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:36:55

Change places for AUDMAST and AUDMONTH tablenames and try again.

E 12°55'05.76"
N 56°04'39.42"

scuzzles
Starting Member

11 Posts

Posted - 2007-07-25 : 16:45:44

Do I have to change am into AUDMAST and x into AUDMONTH? I will play it with.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:50:46

No.

UPDATE		am
SET		am.Y_DIFF = am.Y_DIFF + x.M_DIFF,
		am.Y_ADJ = am.Y_ADJ + x.M_ADJ,
		am.Y_OVER = am.Y_OVER + x.M_OVER,
		am.Y_SHORT = am.Y_SHORT + x.M_SHORT,
		am.Y_NET = am.Y_NET + x.M_NET,
		am.M_DIFF = am.M_DIFF + x.M_DIFF,
		am.M_ADJ = am.M_ADJ + x.M_ADJ,
		am.M_OVER = am.M_OVER + x.M_OVER,
		am.M_SHORT = am.M_SHORT + x.M_SHORT,
		am.M_NET = am.M_NET + x.M_NET
FROM		AUDMAST AS am
INNER JOIN	(
			SELECT		EMP_NO,
					SUM(M_DIFF) AS M_DIFF,
					SUM(M_ADJ) AS M_ADJ,
					SUM(M_OVER) AS M_OVER,
					SUM(M_SHORT) AS M_SHORT,
					SUM(M_NET) AS M_NET
			FROM		AUDMONTH
			GROUP BY	EMP_NO
		) AS x ON x.EMP_NO = am.EMP_NO

E 12°55'05.76"
N 56°04'39.42"

scuzzles
Starting Member

11 Posts

Posted - 2007-07-26 : 09:28:42

Beautiful. U da man. Thank you!

scuzzles
Starting Member

11 Posts

Posted - 2007-09-07 : 12:50:50

Hello again. This updates the table but it seems it doesnt insert any new values for EMP_No if its not already there in AUDMAST. New Employees and data from AUDMONTH arent going into AUDMAST.
Can U help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 12:54:46

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-07 : 13:10:02

Actually the approach in the article could give a wrong error in a multi user environment. I have posted a comment in the articile

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 13:14:28

and as i replied in the comments:
please do show me a better way.

you could of course handle this the problem you pointed out
with isolation levels.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-07 : 13:15:28

And as in my response I have put in the right way

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 13:17:27

well exception handling is pretty heavy duty operation... i do see you point though

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-07 : 13:26:42

Could you explain what is heavy duty in?

insert
if (dupl error)
update

That is not heavy duty in
select
if (not found)
insert
else
update

Or in
update
if (not found)
insert

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 13:34:29

can you show me just how do you implement
if (dupl error)
part?

i thought you were reffering to try catch blocks when you said error handling.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-07 : 13:50:52

create table saad_test ( pk int not null primary key, data varchar(100) )

insert into saad_test values ( 1, 'saad' )

set nocount on
declare @v_pk int, @v_data varchar(100)
begin
set @v_pk = 1
set @v_data = 'ahmad2'
insert into saad_test values ( @v_pk, @v_data )
-- error for duplicate is 2627
if ( @@ERROR = 2627 )
update saad_test set data = @v_data where pk = @v_pk
end

And I will prefer to use true exceptions if available. But even those do not cause any unnecessary overhead. More importantly the code needs to work ...

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 14:00:44

very nice!
could you be so kind to post this in the comments of that blog post?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

scuzzles
Starting Member

11 Posts

Posted - 2007-09-07 : 14:10:52

Im sorta a newbie. How would I use this in my code above. Just use If Exists and an Insert?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 14:18:17

if your application isn't highly transactional you can use exists insert with no problem.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

scuzzles
Starting Member

11 Posts

Posted - 2007-09-07 : 14:59:23

OK, Heres my code thats not inserting. Im getting this error:

The Compound statement SQL construct or statement is not supported.

IF EXISTS (SELECT * FROM AUDMAST LEFT JOIN AUDTRAN ON AUDMAST.EMP_NO = AUDTRAN.EMP_NO)

UPDATE am
SET am.Y_DIFF = am.Y_DIFF + x.M_DIFF, am.Y_ADJ = am.Y_ADJ + x.M_ADJ, am.Y_OVER = am.Y_OVER + x.M_OVER, am.Y_SHORT = am.Y_SHORT + x.M_SHORT, am.Y_NET = am.Y_NET + x.M_NET, am.M_DIFF = am.M_DIFF + x.M_DIFF, am.M_ADJ = am.M_ADJ + x.M_ADJ, am.M_OVER = am.M_OVER + x.M_OVER, am.M_SHORT = am.M_SHORT + x.M_SHORT,am.M_NET = am.M_NET + x.M_NET
FROM AUDMAST AS am INNER JOIN
(SELECT EMP_NO, SUM(M_DIFF) AS M_DIFF, SUM(M_ADJ) AS M_ADJ, SUM(M_OVER) AS M_OVER, SUM(M_SHORT) AS M_SHORT, SUM(M_NET) AS M_NET
FROM AUDMONTH
GROUP BY EMP_NO) AS x ON x.EMP_NO = am.EMP_NO

ELSE
INSERT INTO AUDMAST([EMP_NO], [NAME_FIRST], [NAME_LAST], [M_OVER], [M_SHORT], [M_NET], [M_DIFF], [M_ADJ], [TYPE])
SELECT [EMP_NO], [NAME_FIRST], [NAME_LAST], [M_OVER], [M_SHORT], [M_NET], [M_DIFF], [M_ADJ], [TYPE]
FROM [dbo].[AUDMONTH]

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-07 : 21:52:35

quote:
Originally posted by saad.ahnad@gmail.com

create table saad_test ( pk int not null primary key, data varchar(100) )

insert into saad_test values ( 1, 'saad' )

set nocount on
declare @v_pk int, @v_data varchar(100)
begin
set @v_pk = 1
set @v_data = 'ahmad2'
insert into saad_test values ( @v_pk, @v_data )
-- error for duplicate is 2627
if ( @@ERROR = 2627 )
update saad_test set data = @v_data where pk = @v_pk
end

And I will prefer to use true exceptions if available. But even those do not cause any unnecessary overhead. More importantly the code needs to work ...

--
Saad Ahmad
saad.ahmad@gmail.com

Good solid RBAR

As clever as it is, it's only good for one row at a time... would have to use a loop or some such to do a batch merge.

--Jeff Moden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources