SQL Server UPDATE column value from another table's column values

Question

I have two tables:

T1:

CODE  FULL_NAME

and T2:

CODE   FIRST_NAME   LAST_NAME

all of the columns are of type NVARCHAR.

Some CODE cells in T1 are empty and I need to get them from T2. I need to use FULL_NAME information in T1 and search in LAST_NAME and FIRST_NAME columns in T2. How to write such an UPDATE?

What I wrote so far is:

UPDATE IMPORT_DATA.RDBS_DATA_STORAGE
SET child_iin = pd.iin
FROM IMPORT_DATA.RDBS_DATA_STORAGE
INNER JOIN nedb.PERSONAL_DATA pd ON child_iin LIKE 'N' + '%' + pd.LAST_NAME + '%' + ' ' + 'N' + '%' + 
pd.FIRST_NAME + '%'

WHERE LEN(child_iin) < 1;

But unsuccessful.

EDIT:

I can't just use WHERE t1.FULL_NAME=t2.FIRST_NAME + ' ' + t2.LAST_NAME, because i can have such scenario where t1.FULL_NAME = 'Jorsh Arthur Weasley' and t1.FIRST_NAME='William', t2.LAST_NAME='Weasley'.

Also.

I need to use N, because as i said the columns are type of NVARCHAR and i have non-ASCII letters there, so for example:

SELECT * FROM T1 WHERE CODE = 'Неважно' retruns nothing, to fix this i have to add N: SELECT * FROM T1 WHERE CODE = N'Неважно' returns a row.

"But unsuccessful." What does this mean? Did you get an error, it didn't work as expected, could you elaborate please? — Thom A
– Thom A ♦, Commented Dec 6, 2018 at 11:47

Fahmi · Accepted Answer · 2018-12-06 11:47:09Z

1

You can try below

UPDATE T1 SET T1.CODE = T2.Code 
From T1
Join T2 on t1.FULL_NAME= t2.FIRST_NAME + ' ' +t2.LAST_NAME

answered Dec 6, 2018 at 11:47

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-12-06 11:56:34Z

1

I think you simple need :

UPDATE t1
    SET t1.code = t2.code
FROM t1 INNER JOIN
     t2
     ON t1.FULL_NAME = t2.FIRST_NAME + ' ' + t2.LAST_NAME
WHERE t1.code IS NULL;

answered Dec 6, 2018 at 11:56

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Mukhamedali Zhadigerov Over a year ago

i can't use t1.FULL_NAME = t2.FIRST_NAME + ' ' + t2.LAST_NAME i need to use LIKE because i can have such scenario: FULL_NAME: Jorsh William Weasley and FIRST_NAME:Jorsh, LAST_NAME: Weasley. Can you please update your answer?

Mukhamedali Zhadigerov · Accepted Answer · 2018-12-06 12:58:01Z

0

Here's the answer:

UPDATE IMPORT_DATA.RDBS_DATA_STORAGE
SET child_iin = pd.iin
FROM IMPORT_DATA.RDBS_DATA_STORAGE
       INNER JOIN nedb.PERSONAL_DATA pd
         ON child_iin LIKE '%' + pd.LAST_NAME + '%' + ' ' + '%' + pd.FIRST_NAME + '%'
WHERE LEN(child_iin) < 1;

N's were unnecessary

answered Dec 6, 2018 at 12:58

Mukhamedali Zhadigerov

8814 gold badges15 silver badges35 bronze badges

Collectives™ on Stack Overflow

SQL Server UPDATE column value from another table's column values

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related