troubleshooting Question

SQL Update with DATEDIFF function

Avatar of MrBrownUK
MrBrownUK asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server
4 Comments2 Solutions1729 ViewsLast Modified:
I have written the following SQL to retrieve all records in WEEK_PHASINGS where the shipment period is less than one week.

I then want to update the column PHASINGS in table WEEK_PHASINGS where this is true to 100

select    WEEK_P.SKU_RATIO_ID,
          SH.SHIP_START_DT,
          SH.SHIP_END_DT,
          datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT )AS DATEDIFF_SHIP_ST_END
          
from      SHIP_HIST SH
          inner join      WEEK_PHASINGS WEEK_P
          on              WEEK_P.PROMO_ID_REF = SH.PROMO_ID
          
where     datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT ) = 0

I then want to update the column PHASINGS in table WEEK_PHASINGS where this is true to 100

update    WEEK_PHASINGS
set       PHASING = 100
where     PROMO_ID_REF in (
          select    WEEK_P.SKU_RATIO_ID,
                    SH.SHIP_START_DT,
                    SH.SHIP_END_DT,
                    datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT )AS DATEDIFF_SHIP_ST_END
          from      SHIP_HIST SH
                    inner join      WEEK_PHASINGS WEEK_P
                    on              WEEK_P.PROMO_ID_REF = SH.PROMO_ID

          where     datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT ) = 0);

Could I get some help with my update syntax/statement as it will not execute correctly. Thank you
ASKER CERTIFIED SOLUTION
santhimurthyd
VP

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros