Update using multiple columns

• Sanjay Masawan

  Ten Centuries

  Points: 1031

  More actions

  December 31, 2004 at 4:40 am

  #164110

  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))

   

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  January 2, 2005 at 7:35 pm

  #535573

  The correct query should look something like this:

  UPDATE det

  SET  det.SFDT_QtyMonth1 = tmp.TMSF_QtyMonth1,

    det.SFDT_QtyMonth2 = tmp.TMSF_QtyMonth2,

    det.SFDT_QtyMonth3 = tmp.TMSF_QtyMonth3

  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))

  Aunt Kathi Data Platform MVP
  Author of Expert T-SQL Window Functions
  Simple-Talk Editor