SQL Server Update column using another table column

9rygscc1 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(136)

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

sql-server

来源：https://stackoverflow.com/questions/77260602/update-column-using-another-table-column

2条答案

按热度按时间

gzjq41n41#

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

赞(0）回复(0）举报 2023-10-15

mwecs4sa2#

Really, you should not be using varchar for either of these columns. You're getting formatting differences because you're storing strings when you shouldn't have to worry about format at all because date/time values are not strings.

To deal with your current scenario, where the source or the target can be NULL, an empty string, any number of spaces, or junk, I would do it this way:

UPDATE t1 SET Original_CreatedDate = 
    CONVERT(char(11), TRY_CONVERT(date, t2.CreatedDate), 120)
    + ' ' +
    CONVERT(char(12), TRY_CONVERT(time, t2.CreatedDate), 121)
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2
ON t1.Source = t2.Source
WHERE TRY_CONVERT(datetime, 
  NULLIF(RTRIM(t1.Original_CreatedDate),'')) IS NULL;

Example

But really you should fix those columns so they're the right data type in the first place, or at least protect them with constraints so junk gets rejected. If both columns were datetime , the solution would be much simpler.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Update column using another table column

2条答案

相关问题

热门标签

最新问答