Populate column based on another column in SQL

Question

What's the best way to populate column A with name in df1 based on the common column ID of df1 and df2?

df1

   ID   A 
   122  nan
   233  nan
       ...

df2

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

to get:

   ID   A 
   122  real_name1
   233  real_name2
        ...

Edit: I would also like to change the column name A to new_column_name so the expected output looks like this:

   ID   new_column_name
   122  real_name1
   233  real_name2
        ...

Update:

Tried:

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

and it caught error:

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

Update2:

Table definitions

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]

This looks a lot like Pandas. Are you sure that you're using SQL Server here? — Tim Biegeleisen, Jan 28 '21 at 02:52
Edit your question and include the table definitions for both tables. — Tim Biegeleisen, Jan 28 '21 at 03:31
You need to make the `A` column in the first table _wider_ to accommodate the larger width of the `NAME1` column in the second table. — Tim Biegeleisen, Jan 28 '21 at 03:53
@DaleK hey thanks for the advice. I tried to google but there's mixed information and I didn't want to accidentally break the system thus resorting to help from someone experienced. — nilsinelabore, Jan 28 '21 at 05:02

Tim Biegeleisen · Accepted Answer · 2021-01-28T03:25:03.067

2

If you actually want to update the first table, then use:

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

If you really wanted to express the update logic using a join, you could use an updatable 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;

edited Jan 28 '21 at 03:25

answered Jan 28 '21 at 02:55

Tim Biegeleisen

502,043
27
286
360

Thank you for the answer. Btw do you know a safe way to change column name `A` to `new_column_name`? – nilsinelabore Jan 28 '21 at 03:00
2

@nilsinelabore Just follow [this highly viewed SO question](https://stackoverflow.com/questions/16296622/rename-column-sql-server-2008) for help on that. – Tim Biegeleisen Jan 28 '21 at 03:01
Hi thanks, what does it mean `The multi-part identifier "b.name" could not be bound.`? – nilsinelabore Jan 28 '21 at 03:24

Popeye · Answer 2 · 2021-01-28T02:55:23.290

1

It is simple join.

Select t1.id, t2.name as A
  From df1 t1 join df2 t2 on t1.id = t2.id

Even you don't need df1 in the query. All required values are available in df2.

If there is a case when df2 do not contain some id then use left join.

edited Jan 28 '21 at 02:55

answered Jan 28 '21 at 02:53

Popeye

35,427
4
10
31

3

The way the OP wrote it, it sounds like they want an update rather than a 'view' of the data. – David Brossard Jan 28 '21 at 02:54
2

Got it @DavidBrossard – Popeye Jan 28 '21 at 02:56

Populate column based on another column in SQL

2 Answers2