Its OK No attitude problems with me. Srimanta ----- Original Message ----- From: "Ryan Sabir" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 8:41 PM Subject: RE: Time Required for Updating DB
> The code is missing <CFQUERY etc...> tags around the SQL code... > > Maybe if you tried to understand what the code was doing instead of > blindly pasting it in you would see these problems first. > > No hard feelings, its just that we'd all like to see you become a > better CF coder so we can help you with more interesting problems! > > bye! > > > ----------------------- > Ryan Sabir > Newgency Pty Ltd > 2a Broughton St > Paddington 2021 > Sydney, Australia > Ph (02) 9331 2133 > Fax (02) 9331 5199 > Mobile: 0411 512 454 > http://www.newgency.com/index.cfm?referer=rysig > > > -----Original Message----- > > From: Srimanta [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, 8 October 2002 5:40 PM > > To: CF-Talk > > Subject: Re: Time Required for Updating DB > > > > > > Mosh, > > > > Tried the codes. Still no luck. > > The query runs for about 8 to 10 minutes and then the > > screen shows hundreds of lines of the SQL and no data is updated. > > > > Srimanta > > ----- Original Message ----- > > From: "Mosh Teitelbaum" <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Tuesday, October 08, 2002 8:03 PM > > Subject: RE: Time Required for Updating DB > > > > > > > > Thanks Mosh > > > > > > Yup. 8^) > > > > > > > I am trying to update price field in Table named > > Product with values > > from > > > > field newprice in Table named New. The primary key in > > both the tables is > > > > dealerpart no (Text) which is unique. > > > > Note: All the records need to be updated > > > > <CFQUERY name="UpdatePrice" Datasource="XYZ"> > > > > Select New.newprice,New.dealerpartno,New.publisher, > > > > Product.publisher,Product.dealerpartno,Product.price > > > > >From New,Product > > > > </CFQUERY> > > > > > > > > > > > > <cfloop query="UpdatePrice"> > > > > Update Product > > > > SET Product.price= #UpdatePrice.newprice# > > > > WHERE Product.dealerpartno = #UpdatePrice.dealerpartno# > > > > > > > > </cfloop> > > > > > > > > Note: There are 20000 records in both the tables. > > > > > > Srimanta: > > > > > > There are a few problems here. The first is with your > > SELECT query: > > > Select > > > New.newprice, > > > New.dealerpartno, > > > New.publisher, > > > Product.publisher, > > > Product.dealerpartno, > > > Product.price > > > From > > > New, > > > Product > > > > > > This query (aside from producing two "dealerpartno" > > columns) does not > > > specify how to join the two tables. It therefore creates > > (if I remember > > the > > > terminology correctly) a Cartesian Product of the two > > tables. This is, > > > essentially, a result set of every single possible > > combination of the > > > records from each table. For example, if your 2 tables > > had the following > > > data: > > > New.newPrice Product.price > > > ============ ============= > > > a 1 > > > b 2 > > > > > > the result set would be: > > > newPrice price > > > ============ ============= > > > a 1 > > > a 2 > > > b 1 > > > b 2 > > > > > > So, since both of your tables have 20,000 records, your > > SELECT query is > > > returning a result set with 20,000 x 20,000 records > > (400,000,000 records). > > > Looping 400 million times is probably what's taking so long 8^). > > > > > > Instead, your query should look something like: > > > SELECT > > > New.newprice, > > > New.dealerpartno AS newDealerPartNo, > > > New.publisher AS newPublisher, > > > Product.publisher AS oldPublisher, > > > Product.dealerpartno AS oldDealerPartNo, > > > Product.price > > > FROM > > > New, > > > Product > > > WHERE > > > New.dealerPartNo = Product.dealerPartNo > > > > > > This query will produce a result set with only 20,000 > > records (assuming > > > there's a 1-to-1 match of the part numbers in the 2 tables). > > > > > > All that said, I don't think you need the join in the > > SELECT query. It > > > might be enough to change your code to the following: > > > > > > <CFQUERY NAME="UpdatePrice" DATASOURCE="XYZ"> > > > SELECT > > > newprice, > > > dealerpartno > > > FROM > > > New > > > </CFQUERY> > > > > > > <CFTRANSACTION> > > > <CFLOOP QUERY="UpdatePrice"> > > > UPDATE Product > > > SET price= #newprice# > > > WHERE dealerpartno = #dealerpartno# > > > </CFLOOP> > > > </CFTRANSACTION> > > > > > > -- > > > Mosh Teitelbaum > > > evoch, LLC > > > Tel: (301) 625-9191 > > > Fax: (301) 933-3651 > > > Email: [EMAIL PROTECTED] > > > WWW: http://www.evoch.com/ > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
