Complex updating of fields? - SQL Server Forums

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

Complex updating of fields?

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2013-02-28 : 05:43:28

I'm using a SQL table for email marketing purposes and amongst other fields has these 5 below:

Open, click1, click2, click3 and click4.

The defaults values of these fields are set to '0'. Everytime somebody opens or clicks an email the email sending software incrementally updates the relative field by +1.

At the moment after each campaign I reset all the open and click fields to '0'. This is a loss of data and I can't do much campaign analysis.

I'd like to know if it's possible to copy the field values to another set of fields whilst adding the values together? New fields:

masteropen, masterclick1, masterclick2, masterclick3 and master click4.

Eg.

Open = '5'
Click1 = '3'

Currently

Masteropen = '1'
Masterclick1 = '3'

After the update

Masteropen = '6'
Masterclick1 = '6'

I just need pointing in the right direction.

JT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 05:59:21

sounds like a trigger to me to do automatic summing of values
is this what you're after?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Topaz
Posting Yak Master

199 Posts

Posted - 2013-02-28 : 07:11:47

Visakh16, this sounds like the perfect solution. Not sure how to do this though?

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-28 : 07:14:04

campaign analysis?
Give each row a campaign-no so you have no loss of data at all and you can aggregate everything every time you want.

Too old to Rock'n'Roll too young to die.

Topaz
Posting Yak Master

199 Posts

Posted - 2013-02-28 : 07:19:49

quote:
Originally posted by webfred

campaign analysis?
Give each row a campaign-no so you have no loss of data at all and you can aggregate everything every time you want.

Too old to Rock'n'Roll too young to die.

I appreciate the suggestion but I don't think this is the best solution for us. I like the sounds of a 'trigger with automatic summing'.

JT

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 07:46:57

I like WebFred's suggestion as well. In addition to what he mentioned about the ability to aggregate data at will, if you do the automatic summation, you would have lost the details on which campaign(s) contributed to the totals in each of the summed columns.

Regardless, if you have determined that you want to use the automatic summation, one possibility would be to modify the code where you do the resetting of the click fields to '0' and do the summation immediately before that. So you would have two lines of code instead of one as in:

UPDATE YourTable SET
   masterOpen = ISNULL(masterOpen,0) + open,
   masterClick1 = ISNULL(masterClick1,0) + click1

Topaz
Posting Yak Master

199 Posts

Posted - 2013-02-28 : 10:27:28

Hi James, thanks for the solution. The query sort of works.

I can get MasterOpen to add together open. It doesn't set open back to zero after the query as ran??

JT

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 11:06:15

You are right - the UPDATE only updates the masterXXXX columns. If you want to do the reset as well, add that as well into the query:

UPDATE YourTable SET
   masterOpen = ISNULL(masterOpen,0) + open,
   masterClick1 = ISNULL(masterClick1,0) + click1,
   open = 0,
   click1 = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources