UPDATE with conditions - 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

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE with conditions

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-14 : 19:38:48

Hi, I have a update statement (see below). What logic should I place to ensure only populated fields are getting updated into KPI_DEMO table? Example if Status, StartDates are blank, the update statement for these 2 will not execute, but others will execute if they are populated. Thank you

update kpi_demo
SET [Lead] = TEMPUPDATEKPITBL.Lead
,[Description] = TEMPUPDATEKPITBL.Description
,[ProjectType] = TEMPUPDATEKPITBL.ProjectType
,[Status] = TEMPUPDATEKPITBL.Status
,[RAG] = TEMPUPDATEKPITBL.RAG
,[StartDates] = TEMPUPDATEKPITBL.StartDates
,[EstimatedCompletionDate] = TEMPUPDATEKPITBL.EstimatedCompletionDate
,[Comments] = TEMPUPDATEKPITBL.Comments
,[LastUpdated] = (getdate() )

,StatusCompletedDate= CASE WHEN TEMPUPDATEKPITBL.Status='Completed' THEN GETDATE() END
,[StatusCancelledDate] = CASE WHEN TEMPUPDATEKPITBL.Status='Cancelled' THEN GETDATE() END
,[PropertyIDStartLoc] = TEMPUPDATEKPITBL.PropertyIDStartLoc
,[PropertyIDFinishLoc] = TEMPUPDATEKPITBL.PropertyIDFinishLoc
,[ActualCompletionDate] = TEMPUPDATEKPITBL.ActualCompletionDate
,[Activity] = TEMPUPDATEKPITBL.Activity
,[Manager] = TEMPUPDATEKPITBL.Manager

FROM TEMPUPDATEKPITBL
WHERE TEMPUPDATEKPITBL.TaskID = KPI_DEMO.TaskID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-14 : 19:46:24

You should put condition

AND ISNULL(TEMPUPDATEKPITBL.Status,'') <> '' and ISNULL(TEMPUPDATEKPITBL.Starteddates,'') <> ''

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-14 : 20:29:28

HI, It is not updating fields now at all when I inserted login like this. I have fields populated and some of them not. Updates ar enot happening for other fields which are populated. I just want to ignore all empty values from TEMPUPDATEKPITBL and not to apply them to KPI_DEMO table. Populated values shoudl go through.

update kpi_demo
SET [Lead] = TEMPUPDATEKPITBL.Lead
,[PMO] = TEMPUPDATEKPITBL.PMO
,[RequestingLOB] = TEMPUPDATEKPITBL.RequestingLOB
,[LOB] = TEMPUPDATEKPITBL.LOB
/*,[FinishLocation] = TEMPUPDATEKPITBL.FinishLocation*/
,[Description] = TEMPUPDATEKPITBL.Description
,[ProjectType] = TEMPUPDATEKPITBL.ProjectType
,[ServerName] = TEMPUPDATEKPITBL.ServerName
,[ServerType] = TEMPUPDATEKPITBL.ServerType
,[TechAnalyst] = TEMPUPDATEKPITBL.TechAnalyst
,[Status] = TEMPUPDATEKPITBL.Status
,[RAG] = TEMPUPDATEKPITBL.RAG
,[StartDates] = TEMPUPDATEKPITBL.StartDates
,[EstimatedCompletionDate] = TEMPUPDATEKPITBL.EstimatedCompletionDate
,[Comments] = TEMPUPDATEKPITBL.Comments
,[TimeSpend] = TEMPUPDATEKPITBL.TimeSpend
,[Complexity] = TEMPUPDATEKPITBL.Complexity
,[LastUpdated] = (getdate() )
,[DCOorSTANDALONE] = TEMPUPDATEKPITBL.DCOorSTANDALONE
,[ECM] = TEMPUPDATEKPITBL.ECM

,StatusCompletedDate= CASE WHEN TEMPUPDATEKPITBL.Status='Completed' THEN GETDATE() END
,[StatusCancelledDate] = CASE WHEN TEMPUPDATEKPITBL.Status='Cancelled' THEN GETDATE() END
/*,[CreatedDate] = TEMPUPDATEKPITBL.CreatedDate*/
,[PropertyIDStartLoc] = TEMPUPDATEKPITBL.PropertyIDStartLoc
,[PropertyIDFinishLoc] = TEMPUPDATEKPITBL.PropertyIDFinishLoc
,[RequestType] = TEMPUPDATEKPITBL.RequestType
,[ActualCompletionDate] = TEMPUPDATEKPITBL.ActualCompletionDate
,[Activity] = TEMPUPDATEKPITBL.Activity
,[Manager] = TEMPUPDATEKPITBL.Manager

FROM TEMPUPDATEKPITBL
WHERE TEMPUPDATEKPITBL.TaskID = KPI_DEMO.TaskID

AND ISNULL(TEMPUPDATEKPITBL.Status,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Lead,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.PMO,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Description,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.ProjectType,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.ServerName,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.ServerType,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.TechAnalyst,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Status,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.RAG,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.StartDates,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.EstimatedCompletionDate,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Comments,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.TimeSpend,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Complexity,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.PropertyIDStartLoc,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.PropertyIDFinishLoc,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.RequestID,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.RequestType,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.ActualCompletionDate,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Activity,'') <> ''
AND ISNULL(TEMPUPDATEKPITBL.Manager,'') <> ''

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 02:21:09

Are you wanting to keep the original value if the TEMPUPDATEKPITBL has no value (is NULL)?


[Status] = COALESCE(TEMPUPDATEKPITBL.Status, kpi_demo.Status)
...
,[StartDates] = COALESCE(TEMPUPDATEKPITBL.StartDates, kpi_demo.StartDates)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:10:01

actually
ISNULL(TEMPUPDATEKPITBL.Status,'') <> '' etc
can be simplified as

TEMPUPDATEKPITBL.Status > ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-15 : 11:08:33

thank you - COALESCE version is the one that worked! But thank you all for providing feedback.

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 11:22:38

In case relevant here is a similar question which then needed to handle BLANK, as well as NULL, data (i.e. in the column in TEMPUPDATEKPITBL)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139869

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources