SQL Server Performance Forum – Threads Archive

How to Update the Price field from this master fil

Master_Grade table:
Region Year tobaccoCode Grade CropCode Price
AT 2005 FS A 1 200
AT 2005 FS A 3 250
AT 2005 FS B 1 150
Fact_Bale Table:
BaleNo Region Year tobaccoCode Grade CropCode Price
B1 AT 2005 FS A 1 NULL
B2 AT 2005 FS A 2 NULL
B3 AT 2005 FS A 3 NULL
B4 AT 2005 FS A 4 NULL
B5 AT 2005 FS B 2 NULL
Primary Key for Master_Grade table : Region , Year , tobaccoCode , Grade , CropCode Primary Key for Fact_Bale Table table : Bale No Logical teory:
a)Fact_Bale Table bale no B1 get the price from the master_grade_table as 200.
b)Fact_Bale Table bale no B2 get the price from the master_grade_table still as 200 since can not find the CrodeCode B2 then it should refer to CrodeCode 1 to get the result.
c)Fact_Bale Table bale no B3 get the price from the master_grade_table as 250.
d)Fact_Bale Table bale no B4 get the price from the master_grade_table as 250 since can not find the CrodeCode 4 then it should refer to CrodeCode 3 to get the result.
e) Fact_Bale Table bale no B5 get the price from the master_grade_table as 150 since can not find the CrodeCode 2 then it should refer to CrodeCode 1 to get the result. Question: How to update the Price field at the Fact_Bale Table?
Result should show as below:
Fact_Bale Table:
Bale No Region Year tobaccoCode Grade CropCode Price
B1 AT 2005 FS A 1 200
B2 AT 2005 FS A 2 200
B3 AT 2005 FS A 3 250
B4 AT 2005 FS A 4 250
B5 AT 2005 FS B 2 150
Thanks a lot If you able to help me .Thanks again.

---

try this select: SELECT *
FROM Fact_Bale inner JOIN
Master_Grade ON Fact_Bale.Region = Master_Grade.Region AND Fact_Bale.[Year] = Master_Grade.[Year] AND
Fact_Bale.tobaccoCode = Master_Grade.tobaccoCode AND Fact_Bale.Grade = Master_Grade.Grade AND Fact_Bale.CropCode = Master_Grade.CropCode
union
SELECT *
FROM Fact_Bale left JOIN
Master_Grade ON Fact_Bale.Region = Master_Grade.Region AND Fact_Bale.[Year] = Master_Grade.[Year] AND
Fact_Bale.tobaccoCode = Master_Grade.tobaccoCode AND Fact_Bale.Grade = Master_Grade.Grade AND (Fact_Bale.CropCode-1) = Master_Grade.CropCode
where bale_no in(SELECT bale_no
FROM Fact_Bale left JOIN
Master_Grade ON Fact_Bale.Region = Master_Grade.Region AND Fact_Bale.[Year] = Master_Grade.[Year] AND
Fact_Bale.tobaccoCode = Master_Grade.tobaccoCode AND Fact_Bale.Grade = Master_Grade.Grade AND Fact_Bale.CropCode = Master_Grade.CropCode
where Master_Grade.price is null
)

---

Thanks for the idea.

---

Hi,
Is the above select working fine for you.

---

]]>