update statement with case & select

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 statement with case & select

Author

Topic

j-in-nz
Starting Member

17 Posts

Posted - 2007-03-26 : 19:07:38

UPDATE workflow SET Flag =
CASE
WHEN @Review = 1 then DateQueryRaised
Case
When DateToReview is not null then coalesce(DateQueryRaised,DateToReview)
When DateToReview is null then TestDate
END

WHEN @Review=2 then
Case
When DateToPrelimReview is not null THEN coalesce(DateQueryRaised,DateToPrelimReview)
END
END

I need to change my update statement to include the 2nd field to be DateQueryRaised from another table as per this statement:
Select q.DateQueryRaised
from Workflow w
inner join WorkflowQuery q
ON w.OID = q.Workflow

pls assist
and q.DateQueriesResolved is not null

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-27 : 07:33:56

UPDATE workflow
SET Flag =
CASE
WHEN @Review = 1 then q.DateQueryRaised
Case
When w.DateToReview is not null then coalesce(q.DateQueryRaised,w.DateToReview)
When w.DateToReview is null then w.TestDate
END

WHEN @Review=2 then
Case
When w.DateToPrelimReview is not null THEN coalesce(q.DateQueryRaised,w.DateToPrelimReview)
END
END
from Workflow w
inner join WorkflowQuery q
--left outer join WorkflowQuery q
ON w.OID = q.Workflow

This would need to be a left outer join if the entry in WorkflowQuery might not be there.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources