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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
