UPDATE based on 2 tables - 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 based on 2 tables

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-08 : 21:11:19

HI
I have 2 tables that have the same metadata:

TEMPTABLE:
TaskID (primary key)
Description
PAR
PTDB
Status
CompletedDate
CancelledDate

PRODUCTIONTABLE:
TaskID (primary key)
Description
PAR
PTDB
Status
CompletedDate
CancelledDate

I am trying to write a SQL UPDATE statement that would set CompletedDate or CancelledDate based on value of Status column per TaskID. For example, If Status for TaskID='632' is "Completed" in TEMPTABLE I would not only like to apply that value to ProductionTable but also set CompletedDate column for that TaskID to current date. If Status is "Cancelled" I would like to update CancelledDate not CompletedDate. If Status is anything but Completed or Cancelled I do not want to update CompletedDate or Cancelled date.
Please help Thank you in advance.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-09 : 01:52:05

UPDATE PT
SET PT.CompletedDate= CASE WHEN TT.Status='Completed' THEN GETDATE() END,
PT.CancelledDate= CASE WHEN TT.Status='Cancelled' THEN 'Somevalue' END
FROM PRODUCTIONTABLE PT
INNER JOIN TEMPTABLE TT
ON PT.TASKID=TT.TASKID

jszulc
Yak Posting Veteran

66 Posts

Posted - 2010-02-09 : 15:01:40

Thank you,
was looking for this part:
= CASE WHEN TT.Status='Completed' THEN GETDATE() END

and you helped me 100%.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-10 : 03:16:34

quote:
Originally posted by jszulc

Thank you,
was looking for this part:
= CASE WHEN TT.Status='Completed' THEN GETDATE() END

and you helped me 100%.

you're welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources