sqlsql-serversql-server-2012multiple-columns

Update column using another table column


I have a tables that look something like this

Table1

Source                 OriginalCreatedDate
[email protected]        2022-12-24 06:01:25.000
[email protected]        

Table 2

Source                 CreatedDate
[email protected]        2022-12-24 06:01:25.000
[email protected]        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


Solution

  • 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 = '')