Updates using Case Statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updates using Case Statements

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2009-12-23 : 12:43:54

I've run into a problem with an update statement. I have written a few that usually look like this:

update
[table name]
set
[table name]
from
[table name or location]

my question is what happens when you throw a case when in the set portion of the query. This is a query that was run in access 07 which follows different rules than transact sql.

-original sql-

UPDATE tblShipments
SET tblShipments.[Week Ending calced] = IIf([Weekday]([actual ship date]) Like 1,[actual ship date]+6,
IIf(Weekday([actual ship date]) Like 2,[actual ship date]+5,
IIf(Weekday([actual ship date]) Like 3,[actual ship date]+4,
IIf(Weekday([actual ship date]) Like 4,[actual ship date]+3,

-my modifications-

UPDATE
[tblShipments]
SET
[tblShipments].[Week Ending calced] = CASE WHEN([Weekday].[actual ship date] Like 1 THEN [actual ship date]+6)
FROM
[tblShipments]
CASE WHEN([Weekday].[actual ship date] Like 2 THEN [actual ship date]+5)
WHEN([Weekday].[actual ship date] Like 3 THEN [actual ship date]+4)
WHEN([Weekday].[actual ship date] Like 4 THEN [actual ship date]+3)
END

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-23 : 13:53:02

You dont need CASE I think...try this

UPDATE 
[tblShipments] 
SET 
[tblShipments].[Week Ending calced] = dateadd(day,(7-datepart(weekday,[actual ship date])),[actual ship date])

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-23 : 14:02:10

To make it exactly like the original code then you should add:

where datepart(weekday, [actual ship date]) between 3 and 6

Also, to answer your question on how to structure a CASE statement, here is an example of a SIMPLE CASE:


update s set
       s.[week ending calced] =
              CASE datepart(weekday, [actual ship date])
                     WHEN 1 THEN [actual ship date] + 6
                     WHEN 2 THEN [actual ship date] + 5
                     WHEN 3 THEN [actual ship date] + 4
                     WHEN 4 THEN [actual ship date] + 3
                     END
FROM   [tblShipments] s

Be One with the Optimizer
TG

rob41
Yak Posting Veteran

67 Posts

Posted - 2009-12-23 : 14:08:30

Thanks so much for the help

vijayisonly

TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-23 : 14:28:14

Np.You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources