December 31, 2004 at 4:40 am
I want to update 2 columns the table OPT_SalesForecastDetail using the following
SET det.SFDT_QtyMonth1 = tmp.TMSF_QtyMonth1,
det.SFDT_QtyMonth2 = tmp.TMSF_QtyMonth2,
det.SFDT_QtyMonth3 = tmp.TMSF_QtyMonth3
Using Query below
SELECT hd.SFHD_SalesPlanCode, det.SFDT_LOBCode, det.SFDT_LOBsubCode,
det.SFDT_QtyMonth1, det.SFDT_QtyMonth2, det.SFDT_QtyMonth3,
det.SFDT_PackCode, det.SFDT_VendRemarks, det.SFDT_VersionNo,
det.SFDT_IsNewProduct, 'sanjay', getdate()
FROM OPT_SalesForecastHeader hd INNER JOIN
OPT_SalesForecastDetail det ON hd.SFHD_SalesPlanCode = det.SFDT_SalesPlanCode INNER JOIN
OPT_TMPSalesForecast tmp ON det.SFDT_PackCode = tmp.TMSF_PackCode
WHERE ((hd.SFHD_Month1 = @chMonth1) AND (det.SFDT_LOBCode = @chLOBCode) AND (tmp.TMSF_QtyMonth1 <> det.SFDT_QtyMonth1))
OR ((hd.SFHD_Month1 = @chMonth1) AND (det.SFDT_LOBCode = @chLOBCode) AND (tmp.TMSF_QtyMonth2 <> det.SFDT_QtyMonth2))
OR ((hd.SFHD_Month1 = @chMonth1) AND (det.SFDT_LOBCode = @chLOBCode) AND (tmp.TMSF_QtyMonth3 <> det.SFDT_QtyMonth3))
January 2, 2005 at 7:35 pm
The correct query should look something like this:
det.SFDT_QtyMonth2 = tmp.TMSF_QtyMonth2,
det.SFDT_QtyMonth3 = tmp.TMSF_QtyMonth3
OPT_SalesForecastDetail det ON hd.SFHD_SalesPlanCode = det.SFDT_SalesPlanCode INNER JOIN
OPT_TMPSalesForecast tmp ON det.SFDT_PackCode = tmp.TMSF_PackCode
WHERE ((hd.SFHD_Month1 = @chMonth1) AND (det.SFDT_LOBCode = @chLOBCode) AND (tmp.TMSF_QtyMonth1 <> det.SFDT_QtyMonth1))
OR ((hd.SFHD_Month1 = @chMonth1) AND (det.SFDT_LOBCode = @chLOBCode) AND (tmp.TMSF_QtyMonth2 <> det.SFDT_QtyMonth2))
OR ((hd.SFHD_Month1 = @chMonth1) AND (det.SFDT_LOBCode = @chLOBCode) AND (tmp.TMSF_QtyMonth3 <> det.SFDT_QtyMonth3))
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy