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.
| Author |
Topic |
|
jp2code
Posting Yak Master
175 Posts |
 Posted - 2009-03-06 : 12:38:14
|
Can I update a table with a CASE statement in it? Either I can't do it or I have a syntax error.declare @WorkOrder_Number char(10), @WorkOrder_Seq intset @WorkOrder_Number='xr1200'set @WorkOrder_Seq=0UPDATE ACP_Parts SET WorkOrder_Qty=WorkOrder_Qty - 1, CASE WHEN 0 < @WorkOrder_Seq THEN WorkOrder_Seq=WorkOrder_Seq - 1 ELSE WorkOrder_Seq=WorkOrder_Seq END WHERE CASE WHEN 0 < @WorkOrder_Seq THEN (WorkOrder_Number=@WorkOrder_Number) AND (System_ID Like '%Label%') AND (@WorkOrder_Seq < WorkOrder_Seq) END ELSE (WorkOrder_Number=@WorkOrder_Number) AND (System_ID Like '%Label%') END Query Analyzer only says, "Incorrect syntax near the keyword 'CASE'." and points me to the first use of the CASE (in the Update clause, not the Where clause).Thanks in advance for your help!~Joe
Avoid Sears Home Improvement |
|
|
guptam
Posting Yak Master
161 Posts |
Posted - 2009-03-06 : 12:46:08
|
| Try ..DECLARE @WorkOrder_Number char(10), @WorkOrder_Seq intSET @WorkOrder_Number='xr1200'SET @WorkOrder_Seq=0UPDATE ACP_Parts SET WorkOrder_Qty=WorkOrder_Qty - 1, WorkOrder_Seq=(CASE WHEN 0 < @WorkOrder_Seq THEN WorkOrder_Seq - 1 ELSE WorkOrder_Seq END)WHERE CASE WHEN 0 < @WorkOrder_Seq THEN (WorkOrder_Number=@WorkOrder_Number) AND (System_ID Like '%Label%') AND (@WorkOrder_Seq < WorkOrder_Seq) END ELSE (WorkOrder_Number=@WorkOrder_Number) AND (System_ID Like '%Label%') END-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ |
 |
|
|
jp2code
Posting Yak Master
175 Posts |
|
|
|
|
|
UPDATE table using CASE?