UPDATE, INNER JOIN (maybe) - 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 2000 Forums

Transact-SQL (2000)

UPDATE, INNER JOIN (maybe)

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-24 : 23:45:27

UPDATE #TempTable SET BackGroundColor = '#ffff99'
WHERE (CompletedDate IS NULL AND PlanCompletionDate > @Date
AND PlanCompletionDate > EstimatedCompletionDate AND CorrectiveActionTypeID IS NULL)

What I need to do is in this update Take the value of the first row that BackGroundColor = '#ffff99'
and update ALL rows AFTER IT to BackGroundColor = '#ffff99'
as well. Any thoughts on how I could do that? Thanks

Here is my ddl

CREATE TABLE [dbo].[ProjectEvent](
[ProjectID] [int] NOT NULL,
[EventID] [int] NOT NULL,
[EventOrder] [int] NULL,
[CorrectiveActionTypeID] [int] NULL,
[EstimatedCompletionDate] [datetime] NULL,
[CompletedDate] [datetime] NULL,
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompletedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LeadTime] [int] NOT NULL,
[CorrectiveActionNote] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CorrectiveActionCost] [money] NULL,
[CorrectiveActionPartyResponsible] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PlanCompletionDate] [datetime] NULL,
CONSTRAINT [PK__ProjectEvent__48EFCE0F] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC,
[EventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Harry C

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 23:52:31

It's not quite clear what you want. Can you post some sample data and the result that you want ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-25 : 01:32:05

Somthing like this ??

UPDATE #TempTable SET BackGroundColor = (Select Top 1 BackGroundColor From #TempTable Where
BackGroundColor Is Not Null Order by Pk)
WHERE (CompletedDate IS NULL AND PlanCompletionDate > @Date
AND PlanCompletionDate > EstimatedCompletionDate AND CorrectiveActionTypeID IS NULL)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-25 : 13:26:23

Something like this... But all in one query, if possible

UPDATE #TempTable SET BackGroundColor = '#ffff99'
WHERE CompletedDate IS NULL AND PlanCompletionDate > @Date
AND PlanCompletionDate > EstimatedCompletionDate AND CorrectiveActionTypeID IS NULL

UPDATE #TempTable SET BackGroundColor = '#ffff99'
WHERE EventOrder > (
Select MIN(EventOrder) FROM #TempTable
WHERE BackGroundColor = '#ffff99'
) AND CorrectiveActionTypeID IS NULL

I got this from another developer

UPDATE #TempTable SET BackGroundColor = '#ffff99'
WHERE EventOrder >= (
SELECT MIN(EventOrder)
WHERE CompletedDate IS NULL
AND PlanCompletionDate > @Date
AND PlanCompletionDate > EstimatedCompletionDate
AND CorrectiveActionTypeID IS NULL )

But it throws an error
"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. "

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-27 : 02:00:24

Try this ..


UPDATE #TempTable SET BackGroundColor = '#ffff99'
WHERE EventOrder >= (
SELECT MIN(EventOrder) From #TempTable 
Having CompletedDate IS NULL
AND PlanCompletionDate > @Date
AND PlanCompletionDate > EstimatedCompletionDate
AND CorrectiveActionTypeID IS NULL )

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources