April 25, 2007 at 4:25 pm
Hi i've a reporting table where i import data into and then update the products based on a product name in the table, i have about 50 - to codes were i've to check and then update these in the main table at the moment am doing this in a stored procedure and an example of what am doing is.. below, would it be best to move these codes to a table and maintain it and do an update with a inner join on both tables. am updating 15- 20k a night, my job is running for 1-2 hours.. which is too long..
any advice would be great.
update tbl
set prodname = 'new product name'
where productname = 'xxxxxxx'
another example is
update tbl
set prodname = 'new product name'
where productcode in 'x','y','z' -- this could have 20, 30 codes in it.
April 25, 2007 at 5:09 pm
Francis,
Without having the DDL for the tables, I can only make a suggestion, but it seems that you would be better off creating a Product table and using it as a “typeTable” for the base table.
Ie…
CREATE TABLE dbo.productInfo
(productID CHAR(x) -- or whatever matches your product code
,productName VARCHAR(x))
This way you would reference the product from your main table by its ID. When you need to change a product’s name you will only be affecting one record, rather than each record in your main table.
Another thought, depending if you need history of product names, would be to add a column in the productInfo table that holds the inactive date for the name. you, of course would need to account for this in your queries/sps. This is all really dependant on your business needs, but you did ask for advice.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 26, 2007 at 1:00 pm
Francis, lets assume
1) Import table is tbl_IMPORT which is a cross-reference table containing information on products and one of the fields is ProdID, and NewProdName is the new name
2) The live table, tbl_Live, also has a ProdID and it is the table to maintain.
Then this will do it.
update dbo.tbl_Live
set prodname = case when not(B.NewProdName is null) then B.NewProdname else A.prodname end
from dbo.Live A
left join dbo.tbl_IMPORT B on
A.ProdID = B.ProdID
You need the CASE there or just do an INNER JOIN.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply