Update a column based on the oldest date

Transact-SQL
1

hello,

i have a table with 2 column : ID and Created Date. For the same ID i have different created dates. i need to update the Created Date based on the oldest date on which that ID was created.

image

For example, for ID=1 i have at created date null values, 12 april 2016 and 14 oct 2015. In the final tabel i need to update thsi column for ID=1 only with the oldes date: 14 oct 2015.

Any suggestions?
thank you in advance!

2

I am not able to see the image you posted - probably my firewall something, but check if you can see the image.

3

yes i can see the image :slight_smile:

4 
;WITH cte_min_dates AS (
    SELECT ID, MIN(CreatedDate) AS MinCreatedDate
    FROM dbo.table_name
    GROUP BY ID
    HAVING MIN(CreatedDate) IS NOT NULL
)
UPDATE tn
SET CreatedDate = cmd.MinCreatedDate
FROM dbo.table_name tn
INNER JOIN cte_min_dates cmd ON cmd.ID = tn.ID AND (tn.CreatedDate IS NULL OR cmd.MinCreatedDate <> tn.CreatedDate)
5

thank you, it worked