Updating a lot of rows in a database?

Question

Hey everyone!
Here's what I am doing:
in Databse1 I have item_tbl and itemfile_tbl
item_tbl has 30,000 rows of ID number and a price
itemfile_tbl has 4 rows of ID number and price

I need to update item_tbl with the four rows from itemfile_tbl... I use the following query...

update item_tbl
set sell_price=(select itemfile_tbl.sell_price/100
from itemfile_tbl
where itemfile_tbl.item_id = item_tbl.item_id)
where exists
(select itemfile_tbl.sell_price
from itemfile_tbl
where itemfile_tbl.item_id = item_tbl.item_id
AND
itemfile_tbl.sell_price/100<>item_tbl.sell_price)


My problem is that it takes 10seconds per item to update!
in all reality I will eventually have to update almost all 30,000 rows, but I'm using the 4 as my test data.
This means it will take a VERY long time (~70 hours)

Am I doing something wrong?? Please help, Thanks!

- Jason

I am using a DB2 database

Answer 1

I think you need a join statement from the smaller file to the larger one using the id_number. Are you using Access?

Answer 2

Jason

Just change your WHERE thing, so more than one row is specified in the query

Good luck and Happy Computing