1 Reply Latest reply: Jul 22, 2014 1:25 PM by Carl Von Stetten RSS

    If statement in update query

    TheScarecrow Community Member
    TheScarecrow Jul 22, 2014 1:16 PM

      I was wondering if you could have a cfif statement inside of a update query.  See example below.  Is there a better way of doing it? thanks.

      <cfquery DATASOURCE="xxx" name="update">

        UPDATE plant_gen_info

              SET levels_complete = #URL.var0#

                  <cfif IsDefined("URLvar13">

                  ,Q1_answer = #URL.var13#

                  </cfif>

                

              WHERE ID = #session.member_id#

        </cfquery>

      • 58 Views
      • Tags:
        • 1. Re: If statement in update query
          Carl Von Stetten MeganK
          Carl Von Stetten Jul 22, 2014 1:25 PM (in response to TheScarecrow)

          TheScarecrow,

           

          Yes, dynamic query statements can be assembled using <cfif>.  I would suggest you switch your IsDefined() to a StructKeyExists() and strongly suggest you make good use of <cfqueryparam>:

           

          <cfquery DATASOURCE="xxx" name="update">
  UPDATE plant_gen_info
        SET levels_complete = <cfqueryparam value="#URL.var0#" cfsqltype="****">
            <cfif StructKeyExists(URL, "var13")>
            ,Q1_answer = <cfqueryparam value="#URL.var13#" cfsqltype="****">
            </cfif>
          
        WHERE ID = <cfqueryparam value="#session.member_id#" cfsqltype="****">
  </cfquery>

           

          I put a "****" placeholder for cfsqltype attributes because I'm not sure which would be appropriate for your variables.  See the help docs for more on the cfqueryparam and cfsqltype.

           

          -Carl V.