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

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-11 : 10:28:07

I have a SQL statemnet that updates fields from temporary table to production (see below). I would like to update CommentsTbl table at the same time only if Comments field from TEMPUPDATEKPITBL is populated. If it is blank I don't want to update it CommentsTbl (ignore it). Note: Comments field is present in TEMPUPDATEKPITBL. I just need to copy Comments from TEMPUPDATEKPITBL to CommentsTbl (schema shown below). Thank you
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
update kpi_demo
SET [Lead] = TEMPUPDATEKPITBL.Lead

,[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() )

,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
/*,[TspanStatus] = TEMPUPDATEKPITBL.TspanStatus*/
,[Activity] = TEMPUPDATEKPITBL.Activity
,[Manager] = TEMPUPDATEKPITBL.Manager

FROM TEMPUPDATEKPITBL
WHERE TEMPUPDATEKPITBL.TaskID = KPI_DEMO.TaskID

Delete from TEMPUPDATEKPITBL

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

USE [KPI]
GO
/****** Object: Table [dbo].[CommentsTbl] Script Date: 02/11/2010 09:10:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CommentsTbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[LoginName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [smalldatetime] NULL
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:31:10

[code]
UPDATE c
SET c.Comments =t.Comments
FROM CommentsTbl c
JOIN TEMPUPDATEKPITBL t
ON t.TaskID = c.TaskID
WHERE t.Comments > ''
[/code]

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

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-11 : 11:08:45

quote:
Originally posted by visakh16

UPDATE c
SET c.Comments =t.Comments
FROM CommentsTbl c
JOIN TEMPUPDATEKPITBL t
ON t.TaskID = c.TaskID
WHERE t.Comments > ''

------------------------------------------------------------------------------------------------------
Thx
Can you write INSERT statement for this? I don't want to ovewrite existing Comment for a TaskID.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 11:11:55

[code]
INSERT CommentsTbl (TaskID,Comments)
SELECT TaskID,Comments
FROM TEMPUPDATEKPITBL
WHERE Comments > ''
[/code]

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

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-11 : 12:04:18

Thank you this works. However if I want to add getdate() as Date in CommentsTbl, how would i accomplish this. Note there is no primary key on CommnetsTbl. So it is possible to have rows with same TaskID.

this does not work for me:

INSERT CommentsTbl (TaskID,Comments,Date)
SELECT TaskID,Comments,getdate()
FROM TEMPUPDATEKPITBL
WHERE Comments > ''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 12:06:44

quote:
Originally posted by jszulc

Thank you this works. However if I want to add getdate() as Date in CommentsTbl, how would i accomplish this. Note there is no primary key on CommnetsTbl. So it is possible to have rows with same TaskID.

this does not work for me:

INSERT CommentsTbl (TaskID,Comments,Date)
SELECT TaskID,Comments,getdate()
FROM TEMPUPDATEKPITBL
WHERE Comments > ''

why it doesnt work?

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

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-11 : 13:01:08

Thank you - it works!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 13:06:37

cool

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources