> 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
