title: Populate column based on another column in SQL
body: <p>What's the best way to populate column <code>A</code> with <code>name</code> in <code>df1</code> based on the common column <code>ID</code> of <code>df1</code> and <code>df2</code>?</p>
  <p><code>df1</code></p>
  <pre><code>   ID   A 
     122  nan
     233  nan
         ...
  </code></pre>
  <p><code>df2</code></p>
  <pre><code>   ID   B    name 
     122  10   real_name1
     233  20   real_name2
         ... 
  </code></pre>
  <p>to get:</p>
  <pre><code>   ID   A 
     122  real_name1
     233  real_name2
          ...
  </code></pre>
  <hr />
  <p>Edit:
  I would also like to change the column name <code>A</code> to <code>new_column_name</code> so the expected output looks like this:</p>
  <pre><code>   ID   new_column_name
     122  real_name1
     233  real_name2
          ...
  </code></pre>
  <hr />
  <p>Update:</p>
  <p>Tried:</p>
  <pre><code>UPDATE t1
  SET A = t2.name
  FROM df1 t1
  INNER JOIN df2 t2
      ON t2.ID = t1.ID;
  </code></pre>
  <p>and it caught error:</p>
  <pre><code>Msg 8152, Level 16, State 2, Line 48
  String or binary data would be truncated.
  The statement has been terminated.
  </code></pre>
  <hr />
  <p>Update2:</p>
  <p>Table definitions</p>
  <p><code>df1</code>:</p>
  <pre><code>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]
  </code></pre>
  <p><code>df2</code>:</p>
  <pre><code>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]
  </code></pre>