2 Replies Latest reply on May 22, 2006 5:08 AM by Dan Bracuk

    update add to column

    JohnGree Level 1
      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 ?

        • 1. Re: update add to column
          DJ_Jamba Level 1
          <CFSET Man = 100>

          get the value of 'man' first:

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

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

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

          • 2. Re: update add to column
            Dan Bracuk Level 5
            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