Update column using another table column

Question

I have a tables that look something like this

Table1

Source                 OriginalCreatedDate
james@gmail.com        2022-12-24 06:01:25.000
david@gmail.com        

Table 2

Source                 CreatedDate
james@gmail.com        2022-12-24 06:01:25.000
david@gmail.com        2023-08-07 02:01:25.000

I'm just wondering how can I update Table 1 OriginalCreatedDate column using Table2 CreatedDate column.

I believe I'll need to use the Source column to compare both tables but I'm just wondering how can I update Table1 rows that have null or '' value only. I want to ignore a row that already have OriginalCreatedDate value.

UPDATE Table1
    SET Original_CreatedDate = ISNULL((
        SELECT Top 1 Table2.CreatedDate
        FROM Table2
        WHERE Table2.Source = Table1.Source
    ), Table1.Original_CreatedDate)
        GO

Any help or suggestion would be really appreciated

Answer 1

UPDATE t1
SET
  OriginalCreatedDate = t2.CreatedDate
FROM
  Table1 AS t1
  JOIN Table2 AS t2 ON t1.Source = t2.Source
WHERE
  (t1.OriginalCreatedDate IS NULL) OR (t1.OriginalCreatedDate = '')