I have a following table that I need to update based on records on another one.
My tbl1 columns are Product | Code | Price
MY tbl2 columns are Product | Code | Price | startdate | enddate | Update Type
tbl1 needs to be updated based on records from tbl2
if columns Product and Code are equal for both tables and price is different and current date between startdate and enddate then tbl1 price - tbl2 price and the column tbl2.UpdateType needs to be updated to C.
if columns tbl2.Product and tbl2.Code not found in tbl1 and current date between startdate and enddate then append new records to tbl1. and update the column tbl2.UpdateType to A
if tbl1.Product , tbl1.Code, tbl1.Price not found in tbl2 then delete the whole record from tbl1. and update tbl2.UpdateType to D
I would appreciate some help with the above dilemma.