Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

SQL Update with DATEDIFF function

Avatar of MrBrownUK
MrBrownUK asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
4 Comments1 Solution1729 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
Avatar of santhimurthyd
santhimurthydFlag of United States of America imageVP

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

Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers