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>

[MichaelSJudd]

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

[MichaelSJudd]

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>