Many Thanks Mosh, Thanks for the time taken to look into my problem and for the codes. Will try .
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 Get the mailserver that powers this list at http://www.coolfusion.com
