Ok, that's working (using a different table for the insert,
'hmls_commercial' rather than the 'properties' table for testing)
with this code
insert
into hmls_commercial
( mls_number )
select mls_number
from hmls_commercial_temp
where mls_number
not in ( select substring_index(mls_number, '_', 1 )
as mls_num
from hmls_commercial )
I had to use 'substring_index...' because the mls_number
in the final table is tagged with _h or _s depending on which
MLS the property data is from.
The final issue with this query, however, is how do I insert
values that are not in the table supplying the data.
What I mean is, when I run the query, I need to insert extra
data into the target table, such as:
values ( <cfqueryparam cfsqltype='cf_sql_varchar' value='hmls'>,
<cfqueryparam cfsqltype='cf_sql_varchar' value='#mls_number#_h'> )
How would I write the query so I can specify other values to be inserted?
I had this same problem using the 'dual' function in mysql... it checked for
the duplicates, but I couldn't insert additional values because the column
count didn't match. At least I couldn't figure out a way to do it.
But is it possible to modify your query for the additional values, Jochem?
Thanks,
Rick
> -----Original Message-----
> From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 05, 2008 1:58 PM
> To: CF-Talk
> Subject: Re: Anything wrong with this code?
>
> Rick Faircloth wrote:
> > (Simplified version... MySQL 5)
> >
> > <cfloop query='get_hmls_commercial_data'>
> >
> > <cfquery name='insert_data' datasource='xxxxx'
>
> Do get_hmls_commercial_data and insert_data query the same database? In
> that case you should do:
>
> INSERT INTO properties (mls_number)
> SELECT ...
> FROM ...
> WHERE mls_number NOT IN (
> SELECT mls_number
> FROM properties
> )
>
> Jochem
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:306928
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
