Thanks, I"ll look into that aswell :) Ta T On 3 Jun 2007, at 14:53, Robert Rawlins - Think Blue wrote:
> Well, My suggestion would be to put the weight of the task on the > DB Engine > if possible rather than CF, it'll be much more efficient. > > I'm a SQL Server guy and don't know MySQL very well, but from > memory they > have a REPLACE function, which determines if the record needs to > updated or > inserted, it might be worth you taking a look at it, I think that may > achieve what you want it to, the syntax goes like this. > > REPLACE (col1, col2, col3) > VALUES (val2, val2, val3) > > I think, anyway, It's a bit of a shot in the dark, but just thought > I'd > share it. > > Rob > > -----Original Message----- > From: Tom King [mailto:[EMAIL PROTECTED] > Sent: 03 June 2007 14:44 > To: CF-Talk > Subject: Re: SOT: SQL methods > > that's a fab idea... :) > > Thanks! > > T > > On 3 Jun 2007, at 14:41, Dan O'Keefe wrote: > >> Tom, >> >> One method I have used in the past is when you query the join table >> and >> display the results, store the EventJoinID 's in a list hidden form >> variable. Then on post, wrapped in transaction, a single delete >> statement >> using the EventJoinID 's list will delete all of the old records, >> and then >> loop over your new joins and insert. >> >> It eliminates the need of worrying if a join was changed or >> deleted. You can >> also use <cftry> & <cfcatch> to handle the transaction failing. >> >> Dan >> >> On 6/3/07, Tom King <[EMAIL PROTECTED]> wrote: >>> >>> Hi All, >>> >>> Just set up a MySql DB, and I'm trying to work out the best way to >>> update multiple tables via CF: >>> >>> I.e: >>> >>> TblEvent >>> ------------ >>> EventID [key] >>> EventName >>> >>> tblEventType >>> ------------ >>> EventTypeID [key] >>> EventTypeName >>> >>> tblEventJoin >>> ------------ >>> EventJoinID [key] >>> EventID >>> EventTypeID >>> >>> So above, I might have an EventID, which has multple EventTypeIDs >>> associated with it in tblEventJoin >>> >>> At the moment, the only way I can see of updating the tblEventJoin >>> (assuming I have an EventiD and several EventTypeIDs which are >>> associated) is to Query tblEventJoin, work out whether the Row >>> already exists, if not Insert the record, and if a row exists which >>> shouldnt, delete it. >>> >>> What's the best way to actually do this? Multiple CFQueries, and do >>> the logic CF side, or is there a better method? >>> >>> As you can see, still a newbie on this.. >>> >>> >>> Ta >>> >>> T >>> >>> >>> >>> >>> >>> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279937 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
