Jeez... I forgot to include the CFQUERY tags. I keep telling myself, no more technical emails when I'm exhausted, no more technical emails when I'm exhausted. You'd think I'd finally start listening to myself 8^).
Anyway, just wrap the SQL query in an appropriate CFQUERY tag and you should be fine. It's still probably going to take several minutes to perform (you *ARE* running 20,001 queries). Good luck. -- Mosh Teitelbaum evoch, LLC Tel: (301) 625-9191 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ > -----Original Message----- > From: Srimanta [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, October 08, 2002 3:40 AM > 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
