Creating record to accept counter update

[ACS LLC]

dbase: MS SQL 2005

I want to update a counter using SQL:

UPDATE mycountertable

SET mycounter = mycounter + 1

WHERE thecounter = 1234

If 'thecounter' with the value of 1234 exists in the dbase, then mycounter will update mycounter by 1, however if the record mycounter =1234 does not exist no update will take place

The question I have is do I have to check if thecounter=1234 exists first, then if not create it with an insert, or is anybody aware of a way for SQL to automatically create the record (in this case it would start with value 1) if the record does not exist, in one swoop, without having to go see if it exists first?

Thanks

Mark

[Steve Sommers]

I would create a stored procedure:

CREATE PROCEDURE [incrementTheCounter]

  @id int

AS BEGIN

  declare @counter int

  select @counter = [myCounter] from [myCounterTable] (updlock)

  where [theCounter] = @id

  if @counter is NULL begin

    set @counter = 1

    insert into [myCounter] ( [myCounter], [theCounter] )

    values ( @counter, @id )

  end else begin

    set @counter = @counter + 1

    update [myCounter]

    set [myCounter] = @counter

    where [theCounter] = @id

  end

  select @counter as [theCounter]

  return(0)

END

Then you simply execute the procedure:

exec [incrementTheCounter] @id=1234

and this will return a result set with the incremented counter value. If you don't have the permissions to create the stored procedure, you can break apart the above logic into individual cfquery statements. Just make sure you wrap it all within a cftransaction tag.

[ACS LLC]

wow. I was hoping it might be a little easier than that. Do you think that it would be a lot faster to process that way, than just check if the record exists with a query, then based on that initial check either do an insert query, or an update?

[Steve Sommers]

It depends. If you are incrementing counters a lot, a stored procedure is the most efficient because your sending a single query to the SQL server and recieving back a single result set. If this seldom called, or not called enough to make a significant impact on bandwidth, then doing the logic as two cfquery calls wrapped by a cftransaction may be easier to maintain (stored procedures can be a can of worms to maintain).

[ACS LLC]

unfortunately this is something that is going to occur a lot, although I don't need any feedback from the result, just have the update take place.

I've always avoided stored PROC's, to be honest I found them a little complicated so never bothered too much to learn how to use them, I know that's not ideal but I've managed without them for years

[Steve Sommers]

Another option is just make sure your counter record has been initialized in your table. If this is possible, then you can simply do your original query that does nothing if the record does not exist -- but it'll always exist so no worries.

[ACS LLC]

That was actually how I was going to do it if I could not find a slick, and easy to manage/create SQL command.

I have a large amount of customers that may or may not have counter increases, and I need a multiple counters for each account on each day, so I'd have to have a script that creates a months worth of them, and then at the end of the month removes any with zero value. It means that all updates will go through what could potentially be a lot of empty records on each search for which record to update, and it's also open to error if the script does not create the necessary records each month, as it won't throw and error, the numbers just won't get counted, which is why I wanted to try and have more of a failsafe that also only stored records with values

[Dan_Bracuk]

Why do you need these counters anyway?  Can't you get what you need with the sql count() function?

[ACS LLC]

I need them for daily counts of traffic from many individual affiliates, the issue is that there is a potential for me to add hundreds of thousands of records each day if I record each transaction separately but I don't need  the info to be that detailed, so I figured a counter for each day would have a very significant effect on reducing the number of records with the same desired output

Mark

[Dan_Bracuk]

Add a field to the table that contains affiliate info to store the counts.  Give it a default value of 0.  Then simply add 1 each time you have to and don't worry about conditional logic.

[ACS LLC]

I THINK you are saying what I was going to do? but it would mean advance adding records and always making sure I had them

So if I had 1,000 affiliates, and I need to record a count for each one on a daily basis, I would need a record with zero value set on each one

affiliate,date,counter

1 10/01/2011 0

2 10/01/2011,0

3 10/01/2011 0

4 10/01/2011,0

etc. up to 1,000

then

1 10/02/2011 0

2 10/02/2011,0

3 10/02/2011 0

4 10/02/2011,0

etc. up to 1,000

Or do you have something more efficient?

I know with the above, as long as I make sure the records exist, I can just update the record by 1 with no other logic, but I run the risk of issues if I don't make sure I always have that zero record in place for each day for each affiliate