updating the entire column with datetime values based on other values in the row I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic:


Only add a value to 'ToDateTime' on rows where AID equals 123 and PID equals 2. (rows 2, 4, 5, and 7 below) - otherwise leave 'ToDateTime' as NULL.
If the above condition is met, set/leave 'ToDateTime' as NULL if the data (varchar) cannot be converted to datetime (rows 5 and 7 below have data that will fail datetime conversion)
But if it does contain a value that can be converted to datetime (rows 2 and 4), convert it and update the value in ToDateTime with that converted value.


BEFORE:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | NULL
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | NULL
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL


AFTER:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | 2015-07-21 08:15:06.000
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | 2015-07-19 12:29:42.000
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL


I have been trying a query similar to what is below but it is filling ToDateTime with the same values for every row (in the case of my actual data, NULL.) I'm not sure if something similar to this query is the right approach, or if I'm way off.

UPDATE MyTable SET ToDateTime =
CASE
WHEN ISDATE(myT.cd)= 1 
THEN CONVERT(DATETIME, myT.cd)
ELSE NULL END
FROM (
 SELECT ContainedData cd
 FROM MyTable
 WHERE [AID] = 123 AND [PID] = 2
 ) myT
How about this:

UPDATE [dbo].[MyTable]
SET [ToDateTime] = [ContainedData]
WHERE aid = 123 
and pid = 2 
and isdate([ContainedData] ) = 1