If statement in update query

[TheScarecrow]

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>

[Carl Von Stetten · Accepted solution]

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.