Update Query

lejo

3y987SQL

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tahir Ansari3y ago

Accepted 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

Cr Bhargavi3y ago

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 Murugesan3y ago
```
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.
0

Amit Mohanty3y ago

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;

Tarun3y ago
to restrict multiple ids use pk and use first() function
+1