Not sure about MySQL but this would work in MSSql
IF (NOT EXISTS ( select * from properties where mls_number =
<cfqueryparam cfsqltype='cf_sql_varchar'
value='#get_hmls_commercial_data.mls_number#_h' > )
BEGIN
insert into properties (mls_number)
values (<cfqueryparam cfsqltype='cf_sql_varchar'
value='#get_hmls_commercial_data.mls_number#_h' null='#not
len(trim(mls_number))#'>)
END
Also, I suspect you don't really want the NULL in that cfqueryparam,
but I may be wrong (?)
HTH
Dominic
2008/6/5 Rick Faircloth <[EMAIL PROTECTED]>:
> Kind of a dumb thing I'm trying to do...trying to combine
> an insert statement with a where clause.
>
> I think I need another approach.
>
> Back to the drawing board...
>
> Rick
>
>
>> -----Original Message-----
>> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, June 05, 2008 11:40 AM
>> To: CF-Talk
>> Subject: Anything wrong with this code?
>>
>> (Simplified version... MySQL 5)
>>
>> <cfloop query='get_hmls_commercial_data'>
>>
>> <cfquery name='insert_data' datasource='xxxxx'
>>
>> insert into properties
>>
>> (mls_number)
>>
>> values (<cfqueryparam cfsqltype='cf_sql_varchar'
>> value='#get_hmls_commercial_data.mls_number#_h' null='#not
>> len(trim(mls_number))#'>)
>>
>> where '#get_hmls_commercial_data.mls_number#_h' not in
>> (select mls_number from properties)
>>
>> </cfquery>
>>
>> </cfloop>
>>
>> I'm getting this error upon execution of the template...
>>
>> Error Executing Database Query.
>> You have an error in your SQL syntax;
>> check the manual that corresponds to your MySQL server version
>> for the right syntax to use near
>> 'where '39438_h' not exists in (select mls_number from prope' at line 32
>>
>> ???
>>
>> Thanks,
>>
>> Rick
>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306900
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
