Update a table based on 2 tables

[Sweenster]

Hi, I need to update a table using data from 2 different tables but I am not sure where to start.

T1 contains ALL products and their cost and T2 contains some products but with more up to date costs. [Products in T2 also exist in T1].

Both tables have Prod_Code & Prod_Cost and I need to update the destination table (T3) with the Prod_Code & the most up to date cost. i.e. If product code exists in T2 use the cost from T2 otherwise just use T1.

Any help would be appreciated.

 

[gmmastros]

First, you should build a query that returns all the right costs for you. I suggest a left join with IsNull.

First, does this query work?

Select IsNull(T2.Prod_Code, T1.Prod_Code) As Prod_Code,
       IsNull(T2.Prod_Cost, T1.Prod_Cost) As Prod_Cost
From   T2
       Left Join T1 On T2.Prod_Cost = T1.Prod_Cost

It should return the product code and cost. If the product is in T2, it will return from that table, otherwise, it will get the value from the T1 table.

Once you are convinced that this query works, you can use it to updat the T3 table, like this...

Update T3
Set    T3.Prod_Cost = A.Prod_Cost 
From   T3
       Inner Join (
           Select IsNull(T2.Prod_Code, T1.Prod_Code) As Prod_Code,
                  IsNull(T2.Prod_Cost, T1.Prod_Cost) As Prod_Cost
           From   T2
                  Left Join T1 On T2.Prod_Cost = T1.Prod_Cost 
                  ) As A On T3.Prod_Code = A.Prod_Code

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom