基于SQL中的另一列填充列

Question

根据DF1和DF2的公共列ID在DF1中使用名称填充列A的最佳方法是什么？

   ID   A 
   122  nan
   233  nan
       ...

DF2

   ID   B    name 
   122  10   real_name1
   233  20   real_name2
       ... 

得到：

   ID   A 
   122  real_name1
   233  real_name2
        ...

编辑：我还想将列名A更改为new_column_name，这样预期的输出如下所示：

   ID   new_column_name
   122  real_name1
   233  real_name2
        ...

尝试过：

UPDATE t1
SET A = t2.name
FROM df1 t1
INNER JOIN df2 t2
    ON t2.ID = t1.ID;

它发现了错误：

Msg 8152, Level 16, State 2, Line 48
String or binary data would be truncated.
The statement has been terminated.

更新2：

DF1:

CREATE TABLE [dbo].[df1](
    [ID] [nvarchar](20) NULL,
    [SUB_ID2] [nvarchar](15) NOT NULL,
    [DATE] [datetime2](7) NULL,
    [MONTH] [nvarchar](4000) NULL,
    [QTY] [numeric](20, 8) NOT NULL,
    [SUB_ID] [nvarchar](15) NOT NULL,
    [X] [nvarchar](20) NULL,
    [SUBURB_ID] [nvarchar](30) NULL,
    [PROJECT_NAME] [nvarchar](160) NULL,
    [A] [varchar](10) NOT NULL
) ON [PRIMARY]

DF2:

CREATE TABLE [dbo].[df2](
    [ID] [nvarchar](46) NULL,
    [NAME1] [nvarchar](50) NULL,
    [IDx] [nvarchar](15) NULL,
    [P] [nvarchar](50) NULL,
    [Q] [nvarchar](50) NULL,
    [Z] [nvarchar](50) NULL,
    [Y] [nvarchar](30) NULL,
    [MARK] [nvarchar](10) NULL,

) ON [PRIMARY]

Answer 1

如果您确实想更新第一个表，请使用：

sql prettyprint-override">UPDATE t1
SET A = t2.name
FROM df1 t1
INNER JOIN df2 t2
    ON t2.ID = t1.ID;

如果您真的想使用联接来表达更新逻辑，可以使用可更新的CTE：

WITH cte AS (
    SELECT t1.A, t2.name
    FROM df1 t1
    INNER JOIN df2 t2 ON t2.ID = t1.ID
)

UPDATE cte
SET A = name;