2 Replies Latest reply on Feb 20, 2007 3:26 PM by MichaelSJudd

    Dynamic update record based on field

    sepJosh
      Need help figuring this one out. I need to insert data from one table into another table updating one field depending on what that field is now. Here is the code I was trying but will not work because of the I can't reference a table. Any help or suggestions would be much appreciated.

      <cfquery name="move00065" datasource="dbb_bcb">
      INSERT INTO cust_data
      SELECT t_030db.*
      FROM t_030db
      where store_num30 = '#FORM.numstore#'
      on duplicate key update
      <cfif cust_data.cstmrtyp eq "vf">
      cstmrtyp = 'xc'
      <cfelseif cust_data.cstmrtyp eq "we">
      cstmrtyp='xc'
      <cfelse>
      cstmrtyp='zb'
      </cfif>
      ,result = 'bbbb'
      </cfquery>
        • 1. Re: Dynamic update record based on field
          MichaelSJudd Level 1
          I may not quite understand, but here's what I do; hopefully you can "translate it" to what you are doing specifically.

          Let's assume the form has passed the variable "numstore".

          <cfquery name="checkforalreadythere" datasource="mydatasource">
          SELECT numstore
          FROM table1
          WHERE numstore = '#numstore#'
          </cfquery>
          <cfif checkforalreadythere.RecordCount GT 0>
          JUST PUT YOUR UPDATE QUERY HERE
          <cfelseif checkforalreadythere.RecordCount GT )>
          JUST PUT YOUR INSERT QUERY HERE
          <cfelse>
          JUST PUT ANYERROR HANDLING HERE
          </cfif>

          Anyway, lemme' know!

          - Mike

          • 2. Re: Dynamic update record based on field
            MichaelSJudd Level 1
            Whops! I had a typo in that other one.

            Here you go:

            <cfquery name="checkforalreadythere" datasource="mydatasource">
            SELECT numstore
            FROM table1
            WHERE numstore = '#numstore#'
            </cfquery>
            <cfif checkforalreadythere.RecordCount GT 0>
            JUST PUT YOUR UPDATE QUERY HERE
            <cfelseif checkforalreadythere.RecordCount GT 0>
            JUST PUT YOUR INSERT QUERY HERE
            <cfelse>
            JUST PUT ANYERROR HANDLING HERE
            </cfif>