Increment one field based on the value in another

I need a query to update a value in a table based on another value in the same table.  The table is employees and when the field credits as an amount of 5, I need the field paid_credits to have a value of 5.  When the credits field accumulates to 10, I need the paid_credits field to have the value of 10.  

Just to clarify, when the credits field has a value of 13 for example the value in paid_credits should still be 10 but when credits is = to 15 the value in paid_credits should then be 15 and so on.  Any help is appreciated.  
JohnMac328Asked:
Who is Participating?
 
silvera21Connect With a Mentor Commented:
Update employees
SET credits  = credits + #formfield#
Where employeeID = #form.employeeid#

<!---this can be in the same query block as the update--->
update
employees
set paid_credits = credits
where (credits % 5 = 0) AND employeeID = #form.employeeid#


This will be the simplest solution for you.

Run down of how it works:

Update credits
the second update will mod the value, and if it is has reached a multiple of 5, it will update the value in the paid field.

So if credits = 1, second update will not run (mod = 1)

if credits = 5, second update WILL run, paid_credits is now 5 (mod = 0).

if credits = 7, second update will not run (mod = 2),

if credits = 10, second update WILL run, paid_credits is now 10 (mod = 0)

and so on...
0
 
silvera21Commented:
There are a couple of different ways to do this.

I am guessing this would be using the same code in your last question. I forgot what your form fields were called so this is approximate.

So

Update employees
SET credits  = credits + #formfield#
Where employeeID = #form.employeeid#

<!---this can be in the same query block as the update--->
Select credits Where id = #form.employeeID#

I would then mod (modulus operator % or MOD) the credits field with 5. If the answer is zero, the credit value is a multiple of 5 -- and should then be updated  in the paid_credits field.
0
 
JohnMac328Author Commented:
I am not familiar with MOD, I will look for a syntax example.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
silvera21Commented:
MOD is just like divide, except you get the remainder instead of the normal answer.

so 15 % 5 is 0

16 % 5 is 1

<cfif queryname.credits MOD 5 eq 0>
<!---run update--->
0
 
JohnMac328Author Commented:
I'm not finding anything close to what I am trying.   This can be a stand alone query that could be run once a week on the entire table and would not need to be concerned with a particular employeeid.  Also I don't get how the values are accumulated in the way that was described in the question.  I am something of a novice.
0
 
silvera21Commented:
Oh, I guess I misunderstood your question, I thought it was a tie in to the previous one.

You just need one query then

 update employees set paid_credits = credits where (credits % 5 = 0)

0
 
silvera21Commented:
That is not the best solution. This will lead to holes. The previous method is better.

You will need a more complicated solution to run this once a week.
0
 
JohnMac328Author Commented:
Many thanks.
0
 
JohnMac328Author Commented:
It worked fine in the SQL query but I got this error running it in the program
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]An expression of non-boolean type specified in a context where a condition is expected, near '@P1'.  
  
The error occurred in C:\Inetpub\wwwroot\CF_Library_TestSQL\AddTestDetails.cfm: line 37
 
35 :      UPDATE Employees
36 : SET paid_credits = credits
37 : WHERE (Credits % 5 = 0) AND <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.employeeID#">
 

Open in new window

0
 
silvera21Commented:
CF errors usually show the first line of the query (35) regardless of where the error is in the query.

The error is because you removed the employeeid = before the cfqueryparam.

So you basically have

WHERE (Credits % 5 = 0) AND #form.employeeid#
0
 
JohnMac328Author Commented:
Got it, thanks

WHERE (Credits % 5 = 0)  AND EmployeeID = #val(form.EmployeeID)#
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.