So are you saying that you want multiple rows in the EXT data? If so, you'll have to use both the repID and the ID field to isolate down to one row..
If you want to update multiple rows at one time.. you'll have to loop over the update statement, passing the RepID and the ID On 2/8/07, John Eubanks <[EMAIL PROTECTED]> wrote: > >is RepID a primary key? I'm wondering what's causing the duplication > >in your data. I showed an example in a previous email where the reps > >were getting duplicated. > >Your code looks okay, I'd suggest using <cfqueryparam in your queries, > >however the queries look okay at first glance.. I'd look at your data > >and make sure none is duplicated there... > > > >basically, goto your db, query analyzer or whatever you use to run > >your queries and run this > > SELECT RepFinder_Basic.*,RepFinder_Ext.* > > FROM RepFinder_Basic,RepFinder_Ext > > WHERE RepFinder_Basic.RepId IN (7149) > > AND RepFinder_Basic.RepId = RepFinder_Ext.RepId > > > >Should you see multiple rows being returned? > > > >then > >select * from repfinder_ext > >where repid = 7149 > > > >and > >select * from repfinder_basic > >where repid = 7149 > > > >I'm betting one of those 2 tables will have duplicates.. probably the > >ext table.. > > > > > Greg, you would be correct, for the most part. The repfinder_ext table > contains multiple rows for each person (in this case, 7149 returns 9 rows, > with the only with the only things different being BegZip and EndZip). Id is > the primary key on each table. However, because each table has a different > amount of rows . . . Also, I just changed the query some, to account for the > fact that the zip codes are not the same: > > <cfquery name="Update" datasource="3iReps"> > Update > RepFinder_Basic.FirstName,RepFinder_Basic.LastName,RepFinder_Basic.Region,RepFinder_Basic.Region,RepFinder_Basic.RepId,RepFinder_Ext.FirstName,RepFinder_Ext.LastName,RepFinder_Ext.Region,RepFinder_Ext.RepId,RepFinder_Ext.Phone,RepFinder_Ext.Ext,RepFinder_Ext.BegZip,RepFinder_Ext.EndZip > Set RepFinder_Basic.FirstName = > '#Form.RepFirstName#',RepFinder_Basic.LastName = > '#Form.RepLastName#',RepFinder_Basic.Region = > '#Form.RepRegion#',RepFinder_Basic.RepId = > '#Form.RepId#',RepFinder_Ext.FirstName = > '#Form.RepFirstName#',RepFinder_Ext.LastName = > '#Form.RepLastName#',RepFinder_Ext.Region = > '#Form.RepRegion#',RepFinder_Ext.RepId = '#Form.RepId#',RepFinder_Ext.Phone = > '#Form.Phone#',RepFinder_Ext = '#Form.Ext#',RepFinder_BegZip = > '#Form.BegZip#',EndZip = '#Form.EndZip#' > WHERE RepFinder_Ext.BegZip IN (#Form.BegZip#) AND > RepFinder_Ext.EndZip IN (#Form.EndZip#) > </cfquery> > > > I went ahead and put a link to dump the data so that you can see the the > fields in the repfinder_basic table and the fields in the repfinder_ext > table. http://inside.3i-online.com/reps_test/ > > > >On 2/8/07, John Eubanks <[EMAIL PROTECTED]> wrote: > >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2500 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
