SQL UPDATE - increment values in db

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL UPDATE - increment values in db

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-11-01 : 10:16:44

I have a SQL UPDATE statement that looks like this:

UPDATE [KPITbl] SET [Manager]=@Manager,[Lead] = @Lead, [WRM] = @WRM, [PTDB] = @PTDB, [PAR] = @PAR, [PMO] = @PMO, [RequestingLOB] = @RequestingLOB, [LOB] = @LOB,[PropertyIDStartLoc]=@PropertyIDStartLoc, [TspanID]=@TspanID,[SDMSRec]=@SDMSRec,[RequestID]=@RequestID,[PropertyIDFinishLoc]=@PropertyIDFinishLoc,[Description] = @Description, [ProjectType] = @ProjectType, [ServerName] = @ServerName, [ServerType] = @ServerType,[DCOorSTANDALONE]=@DCOorSTANDALONE,[TechAnalyst] = @TechAnalyst, [Status] = @Status, [RAG] = CASE WHEN @Status='Completed' THEN 'C' WHEN @Status='Cancelled' THEN 'C' ELSE @RAG END, [StartDates] = @StartDates, [EstimatedCompletionDate] = @EstimatedCompletionDate,[ActualCompletionDate] = @ActualCompletionDate,[Activity]=@Activity, [TreeOrDomainImpacted] = @TreeOrDomainImpacted, [NumOfSites] = @NumOfSites, [NumOfUsers] = @NumOfUsers, [GBdatamoved] = @GBdatamoved, [GBdatadeleted] = @GBdatadeleted, [NumOfSrvrsAdded] = @NumOfSrvrsAdded, [EUTEngineeringConsult] = @EUTEngineeringConsult, [Comments] = @Comments, [TimeSpend] = @TimeSpend, [Complexity] = @Complexity, [ECM]=@ECM, [LastUpdated]=(getdate() ),[StatusCompletedDate]=CASE
WHEN @Status='Completed' THEN ISNULL([StatusCompletedDate],GETDATE()) END,[StatusCancelledDate]=CASE
WHEN @Status='Cancelled' THEN ISNULL([StatusCancelledDate],GETDATE()) END WHERE [TaskID] = @TaskID

Based on existing values in DB (that are always 0 or bigger integers )I would like to increment data for [NumOfUsers],[GBdatamoved],[GBdatadeleted].

Ex. When user invokes update button to enter [GBdatamoved] , this select statement takes the existing value form the database and increments it with the data entered by the user. Ex. was 100GB before update , is 110 GB after update beacuse user moved 10gb of data.
Any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 11:49:52

is this what you want?

UPDATE [KPITbl] 
SET [Manager]=@Manager,
[Lead] = @Lead, 
[WRM] = @WRM, 
[PTDB] = @PTDB, 
[PAR] = @PAR, 
[PMO] = @PMO, 
[RequestingLOB] = @RequestingLOB, 
[LOB] = @LOB,
[PropertyIDStartLoc]=@PropertyIDStartLoc, 
[TspanID]=@TspanID,
[SDMSRec]=@SDMSRec,
[RequestID]=@RequestID,
[PropertyIDFinishLoc]=@PropertyIDFinishLoc,
[Description] = @Description, 
[ProjectType] = @ProjectType, 
[ServerName] = @ServerName, 
[ServerType] = @ServerType,
[DCOorSTANDALONE]=@DCOorSTANDALONE,
[TechAnalyst] = @TechAnalyst, 
[Status] = @Status, 
[RAG] = CASE WHEN @Status='Completed' OR @Status='Cancelled' THEN 'C' 
             ELSE @RAG 
        END, 
[StartDates] = @StartDates, 
[EstimatedCompletionDate] = @EstimatedCompletionDate,
[ActualCompletionDate] = @ActualCompletionDate,
[Activity]=@Activity, 
[TreeOrDomainImpacted] = @TreeOrDomainImpacted, 
[NumOfSites] = @NumOfSites, 
[NumOfUsers] = [NumOfUsers] + @NumOfUsers, 
[GBdatamoved] = [GBdatamoved] + @GBdatamoved, 
[GBdatadeleted] = [GBdatadeleted] + @GBdatadeleted, 
[NumOfSrvrsAdded] = @NumOfSrvrsAdded, 
[EUTEngineeringConsult] = @EUTEngineeringConsult, 
[Comments] = @Comments, 
[TimeSpend] = @TimeSpend, 
[Complexity] = @Complexity, 
[ECM]=@ECM, 
[LastUpdated]=(getdate() ),
[StatusCompletedDate]=CASE WHEN @Status='Completed' 
                           THEN ISNULL([StatusCompletedDate],GETDATE())
                      END,
[StatusCancelledDate]=CASE WHEN @Status='Cancelled' 
                           THEN ISNULL([StatusCancelledDate],GETDATE()) 
                      END 
WHERE [TaskID] = @TaskID

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-11-01 : 13:49:08

I had that in my test by it puts NULL value anytime when I do the update.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-01 : 15:42:17

Use it like this:

quote:

Originally posted by visakh16

is this what you want?

UPDATE [KPITbl] 
SET [Manager]=@Manager,
[Lead] = @Lead, 
[WRM] = @WRM, 
[PTDB] = @PTDB, 
[PAR] = @PAR, 
[PMO] = @PMO, 
[RequestingLOB] = @RequestingLOB, 
[LOB] = @LOB,
[PropertyIDStartLoc]=@PropertyIDStartLoc, 
[TspanID]=@TspanID,
[SDMSRec]=@SDMSRec,
[RequestID]=@RequestID,
[PropertyIDFinishLoc]=@PropertyIDFinishLoc,
[Description] = @Description, 
[ProjectType] = @ProjectType, 
[ServerName] = @ServerName, 
[ServerType] = @ServerType,
[DCOorSTANDALONE]=@DCOorSTANDALONE,
[TechAnalyst] = @TechAnalyst, 
[Status] = @Status, 
[RAG] = CASE WHEN @Status='Completed' OR @Status='Cancelled' THEN 'C' 
             ELSE @RAG 
        END, 
[StartDates] = @StartDates, 
[EstimatedCompletionDate] = @EstimatedCompletionDate,
[ActualCompletionDate] = @ActualCompletionDate,
[Activity]=@Activity, 
[TreeOrDomainImpacted] = @TreeOrDomainImpacted, 
[NumOfSites] = @NumOfSites, 
[NumOfUsers] = coalesce([NumOfUsers],0) + @NumOfUsers, 
[GBdatamoved] = coalesce([GBdatamoved],0) + @GBdatamoved, 
[GBdatadeleted] = coalesce([GBdatadeleted],0) + @GBdatadeleted, 
[NumOfSrvrsAdded] = @NumOfSrvrsAdded, 
[EUTEngineeringConsult] = @EUTEngineeringConsult, 
[Comments] = @Comments, 
[TimeSpend] = @TimeSpend, 
[Complexity] = @Complexity, 
[ECM]=@ECM, 
[LastUpdated]=(getdate() ),
[StatusCompletedDate]=CASE WHEN @Status='Completed' 
                           THEN ISNULL([StatusCompletedDate],GETDATE())
                      END,
[StatusCancelledDate]=CASE WHEN @Status='Cancelled' 
                           THEN ISNULL([StatusCancelledDate],GETDATE()) 
                      END 
WHERE [TaskID] = @TaskID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources