Update Query

Question

i want to update new field in 'ActualDate' field in 'tbl_Task' table . fetch data from 'tbl_TaskHistory' 'CreatedDate' field .Task ID is commen in two table. Same task id exist multiple times in task history. i want to fetch first data from task History table based on created date asc.

Tahir Ansari · Answer

UPDATE tbl_Task SET ActualDate = th.CreatedDate FROM tbl_Task t INNER JOIN ( SELECT TaskID, MIN(CreatedDate) AS CreatedDate FROM tbl_TaskHistory GROUP BY TaskID ) th ON t.TaskID = th.TaskID

Amit Mohanty · Answer

UPDATE t SET t.ActualDate = h.CreatedDate FROM tbl_Task t JOIN ( SELECT TaskID, CreatedDate, ROW_NUMBER() OVER (PARTITION BY TaskID ORDER BY CreatedDate ASC) as rn FROM tbl_TaskHistory ) h ON t.TaskID = h.TaskID AND h.rn = 1;

Tarun · Answer

to restrict multiple ids use pk and use first() function

Cr Bhargavi · Answer

UPDATE tbl_Task SET ActualDate = th.CreatedDate FROM tbl_Task AS t INNER JOIN ( SELECT TaskID, MIN(CreatedDate) AS CreatedDate FROM tbl_TaskHistory GROUP BY TaskID ) AS th ON t.TaskID = th.TaskID WHERE t.ActualDate IS NULL OR t.ActualDate > th.CreatedDate;

Manikandan Murugesan · Answer

UPDATE t SET t.ActualDate = th.CreatedDate FROM tbl_Task t INNER JOIN ( SELECT TaskID, CreatedDate, ROW_NUMBER() OVER (PARTITION BY TaskID ORDER BY CreatedDate ASC) AS rn FROM tbl_TaskHistory ) th ON t.TaskID = th.TaskID AND th.rn = 1; To fetch the earliest record from the tbl_TaskHistory table based on the CreatedDate for each TaskID and update the corresponding ActualDate in the tbl_Task table, you can use a SQL query with the ROW_NUMBER() function to rank the records by CreatedDate in ascending order and then perform an update.