update add to column

[Guest]

hi i have a number column called "man" i also have a update query which i need to add to this column

set Man = '100'

this changes the value to 100

what i need is to add 100 to the number already in the column

not sure how to do this ?

[DJ_Jamba]

<CFSET Man = 100>

get the value of 'man' first:

<CFQUERY NAME="getValueofMan" DATASOURCE="#theDSN#">
SELECT man FROM theTable WHERE id = #theUniqueID#
</CFQUERY>

<CFIF getValueofMan.recordcount NEQ 0>
<CFSET Man = Man + getValueofMan.man>
</CFIF>

<CFQUERY NAME="updateValueofMan" DATASOURCE="#theDSN#">
UPDATE theTable set man = #Man# WHERE id = #theUniqueID#
</CFQUERY>

Jam

[Dan_Bracuk]

This is the easiest way and will work as long as man is not null to start with.

update thetable
set man = man + 100
where whatever

If you want to treat nulls as zero, and your db supports this syntax, this should work

update thetable
set man = coalesce(man, 0) + 100
where whatever